Excel – Counting unique values in column A based on criteria in column B

microsoft excelworksheet-function

I would appreciate some assistance with crafting a formula that will count the number of unique text values in column A, that have a certain value in column B.

For example, if I wanted to count all unique fruits (Column A) that are Yuck (Column B), the output would equal 2 (Orange and Pear).

Column A       Column B  
Apple          Yum 
Orange         Yuck  
Pear           Yuck  
Pear           Yuck

Best Answer

I was able to achieved the desired results via the following formula:

=SUMPRODUCT((B:B="Yum")/IF(COUNTIFS(A:A,A:A,B:B,"Yum")=0,1,COUNTIFS(A:A,A:A&"",B:B,"Yum")))

Related Question