Counting up how many different values are in a column?

**Cmorgan** · 01-27-2012, 06:00 PM

Evening,

I have a column which contains about 900 entries/names, out of these 900 entries some are duplicates. I'm looking for a formula that will count the number of different values, e.g. not count duplicates.

Any suggestions would be greatfully appreciated.

Many thanks

**Paul** · 01-27-2012, 06:42 PM

Hi Cmorgan,

Quickest way would probably be to select the range of cells, then using Advanced Filter copy the list and paste unique values to another column. It would then be easy to use a COUNTA function on that new range to show you how many values exist.

**daddylonglegs** · 01-27-2012, 06:49 PM

For data in A2:A1000 try this formula

=SUMPRODUCT((A2:A1000<>"")/COUNTIF(A2:A1000,A2:A1000&""))

**Paul** · 01-27-2012, 07:11 PM

Nice one DDL. Figured out the &"" bit accounts for blanks in the range.

**Cmorgan** · 01-27-2012, 07:36 PM

Thanks this works a treat, just to complicate matters, using your formula of:

=SUMPRODUCT((Input!C:C<>"")/COUNTIF(Input!C:C,Input!C:C&""))

is there anyway to adapt this so it only does this is B4 is in column H?

Many thanks

**Paul** · 01-27-2012, 07:42 PM

Do you mean only do this calculation if the value in cell B4 is found somewhere in column H? If so, try..

=IF(ISNUMBER(MATCH(B4,H:H,0)),SUMPRODUCT((Input!C:C<>"")/COUNTIF(Input!C:C,Input!C:C&"")),"")

**Haseeb Avarakkan** · 01-27-2012, 08:18 PM

if you are looking to count unique entries in Column C, if column H = B4, if so try this,

=SUMPRODUCT(--(C2:C1000<>""),--(H2:H1000=B4),--(MATCH(C2:C1000&"_"&H2:H1000,C2:C1000&"_"&H2:H1000,0)=ROW(C2:C1000)-ROW(C2)+1))

Do not use whole column reference like C:C, use a dynamic range instead or limit the range.