Unique count with another condition

[not very bright dave]

Hi all

I understand that the following can be used to generate the number of unqiue values in an array:

=SUMPRODUCT((A1:A20<>"")/(COUNTIF(A1:A20,A1:A20)+(A1:A20="")))

However, what if you want to count only the unique values that have another condition attached in another array (for example, in B1:B20 I have a list of names and I only want to know the numbber of unique items for 'John')?

This has been driving me crazy all morning and I've finally given up and decided to ask the experts!

Thanks for your help

Dave

 

[Domenic]

Try...

=SUM(IF(FREQUENCY(IF(B1:B20="John",IF(A1:A20<>"",MATCH("~"&A1:A20,A1:A20&"",0))),ROW(A1:A20)-ROW(A1)+1),1))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[not very bright dave]

That's fantastic Domenic, thanks very much (I'll be a while trying to understand it I think).