Excel - Count 2 values in one column and 1 value in other

March 2017




Issue


I have two column lists (two values in first column and one value in the other)

e.g. Column A has the following values: A B C B B A C

Column B has the following values: Yes


I need a formula to count how many A's & B's there are if column B is = Yes

Note


Suppose sample data is like this (row 1 has headings H1 and H2)

H1 H2 
A YES 
B YES 
C 
B YES 
B YES 
A 
C YES 



Try this formula in an empty cell

=SUMPRODUCT((A2:A8="B")*(B2:B8="yes"))

you will get 3

Note


Thanks to venkat1926 for this tip on the forum.

Related


Published by aakai1056.
This document, titled "Excel - Count 2 values in one column and 1 value in other," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).