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

Question

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  

Answer 1

=SUMPRODUCT(--(FREQUENCY(MATCH(A:A&B:B,A:A&"Yuck",0),ROW(A:A)-ROW(A1)+1)>0))   

---

Explanations :

Frequency will count unique values that derive from the Match

Match is used to get the position of each item in the Data and only returns the first match of a duplicate item which appear more then once in the Data

The bins array of the Frequency is built from ROW(A:A)-ROW(A1)+1 to result in a sequential array like {1;2;3;4;5;6;7;8;9;10}

the -- before frequency to return 1 when the >0 is True

Sumproduct will sum the 1

A:A&B:B is the value to match with A:A&"Yuck" :
AppleYuck, OrangeYuck, OrangeYum to Match with AppleYuck, OrangeYuck ...
and return the row number

Answer 2

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")))

Answer 3

You need an Array Formula to solve the issue:

• An Array Formula in Cell C46, finish with Ctrl+Shift+Enter.

  =CEILING(SUM(SUMPRODUCT(($A$40:$A$46=A40)*($B$40:$B$46=B40))/(COUNTIFS($A$40:$A$46,A40,$B$40:$B$46,B40)+($A$40:$A$46<>A40)+($B$40:$B$46<>B40))),1)
  

You may adjust cell references in the formula as needed.