Counting unique values - based on another column!

QuestionEdit

so here's the data: column A contains a drop-down list of three text values (Apple, Pear, Orange). column B contains names - some of which may be repeated.

what i would like to do is count the number of unique values in column B for EACH of the values in column A - leaving me with three unique sums. For example, there are three occurrences of the same name in column B where Appeal has been chosen in column A. I only want to count that name once.

Does my question make sense? I found tons of formulae online about counting unique text values, but none about counting unique text values BASED ON text values in another column ...

Thanks in advance! :)

Sandy

AnswerEdit

Sandy,

Say the data with Apple, Pear, Orange are located in A2:A22 the data with the values to count uniques are located in B2:B22

for Apples: =SUM(IF(FREQUENCY(IF((LEN($B$2:$B$22)>0)*($A$2:$A$22="Apple"),MATCH($B$2:$B$22,$B$2:$B$22,0),""), IF((LEN($B$2:$B$22)>0)*($A$2:$A$22="Apple"),MATCH($B$2:$B$22,$B$2:$B$22,0),""))>0,1))

for Pears:

=SUM(IF(FREQUENCY(IF((LEN($B$2:$B$22)>0)*($A$2:$A$22="Pear"),MATCH($B$2:$B$22,$B$2:$B$22,0),""), IF((LEN($B$2:$B$22)>0)*($A$2:$A$22="Pear"),MATCH($B$2:$B$22,$B$2:$B$22,0),""))>0,1))

for Oranges: =SUM(IF(FREQUENCY(IF((LEN($B$2:$B$22)>0)*($A$2:$A$22="Orange"),MATCH($B$2:$B$22,$B$2:$B$22,0),""), IF((LEN($B$2:$B$22)>0)*($A$2:$A$22="Orange"),MATCH($B$2:$B$22,$B$2:$B$22,0),""))>0,1))

each of these need to be entered with Ctrl+Shift+Enter (hold down the control and shift keys while you hit Enter) rather than just enter since these are Array formulas.

Is that what you want.

The above were tested under the stated assumptions and they worked for me.