Counting Unquie Records

[Jayfunk]

Hello Guys,

I have a Data set which is a bit like this

Product Sales Person
A Mr Smith
A Mr Smith
A Mr Smith
B Mr Brown
C Mrs Pink
D Mr Smith

What i am trying to do is count how many Products Mr Smith has, if i use the simple count ifs then i would get 4 when the answer is 2, I believe i need to change things in the countifs formula but i am not sure how to do so, also i would need to understand how it works as i get this alot, does anyone of you fine people know an answer to my problem, the simplier the better

So the data set doesn't look right on the screen Product and Sales Person is in two different columns

 

[davesweep]

Hello,

Presuming your data table starts in A1,

=SUMPRODUCT((B2:B7="Mr Smith")/COUNTIF(A2:A7,A2:A7))

Will return 2; the number of unique entries for Mr Smith.