Count unique value occuirences

Hello all

I have a large list of data (shrunk for example purpose here)

I have done a uniqe sort in column C.. in column D, I want a formula to count the number of unique times the value appears in column A.. so 'Paco Rabanne' for example, I want '1' to be returned instead of 5......

Can someone help please?

is this what you want?
=COUNTIF($A$2:$A$21,C2)

Hi Sam

I got a value of '3' returned' for '& soda' - whereas I want it to be like a count of how many unique values exist... so I don't want to have '& soda' counted 3 times, I want a value of '1' to be returned to say the value '& soda' is one unique value, then James Bond 007 and so on...

so you are saying that no matter how many times James Bond 007 appears, you want the count to be 1 as long as it appears at least once?
if true then this...? =IF(COUNTIF($A$2:$A$21,C2)>0,1,0)
not crazy about the formula as I think there is a better formula in office 365 using unique but not in my version of excel.

or if you are looking for an overall count of unique values in the list in col A, this? =SUMPRODUCT(1/COUNTIF(A2:A21,A2:A21))

something like this?

Name	Count	Count Distinct
& Soda	3	1
James Bond 007	7	1
Paco Rabanne	5	1
10 Minute	4	1
Paco Rabannes1	1	1

or

CountUnique

5

In C2

Formula:

Please Login or Register  to view this content.

If you want 1 instead of actual count

Formula:

Please Login or Register  to view this content.

To count unique values in column A. In F2.

Formula:

Please Login or Register  to view this content.

THANK YOU SAM YOUR POST AT 12.34 pm helped!

Glad I was able to help, AND thank you for the rep!