Excel: count unique values using SUMPRODUCT with multiple conditions

Question

Ok, I have a sum product working to give me the count of unique values in a column:

=SUMPRODUCT((F2:F38<>"")/COUNTIF(F2:F38,F2:F38))

So if I have the numbers: 1, 2, 3, 1, 5, 6, 2, 5, 2 it would return 5.

But what I want to do is count the number of unique values based upon another number, e.g.:

Name:             Sales:
Mike              2
Bob               1
Gary              1
Mike              5
Bob               6
Gary              1
Mike              3
Bob               4
Gary              2
Mike              1
Bob               2
Gary              6
Mike              3
Bob               1
Gary              1
Mike              1
Bob               3
Gary              4

It would say that there are 4 unique values for "Name" Mike, 5unique values for "Name" Bob, and4unique values for "Name" Gary. This is because for "Name" Mike there are the numbers253131, so the unique numbers are2531and therefore the count would return4`.

Is there a way of doing this?

Answer 1

Here's one solution.

Insert a cell at the bottom with the value you are trying to count unique for.. So "Gary" at the bottom of your sheet. Create another column to the right of the Sales Column Called "NameValue". Create a formula in this column:

=IF(A2=$A$10,B2,"") <-Where $A$10 is the location of "Gary"

Then at the bottom of the new column insert this formula:

=SUM(IF(C2:C6<>"",1/COUNTIF(C2:C6, C2:C6), 0)) <-Where C2:C6 is your range we just create from above.

You might need to press Ctrl+Shift+enter when you type this formula in. Now you can change the value of A12 to any value in the name column and it will spit out the unique count.