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

December 2016




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 :

This document entitled « Excel - Count 2 values in one column and 1 value in other » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.