Counting unique values

[Bob]

I need to count the number of unique cell values in a
column, any ideas how to accomplish this?

 

[Bob Phillips]

=SUM(1/COUNTIF(A1:A10,A1:A10))

it's an array formula, so commit with Ctrl-Shift-Enter, not Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Peo Sjoblom]

One way

=SUM(IF(A2:A200<>"",1/COUNTIF(A2:A200,A2:A200)))

entered with ctrl + shift & enter

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Ken Wright]

One way assuming no blanks:-

=SUMPRODUCT(1/COUNTIF($A$1:$A$100,$A$1:$A$100))

For more detail and options:-

http://www.cpearson.com/excel/duplicat.htm#CountingUnique

 

[Harlan Grove]

One way

=SUM(IF(A2:A200<>"",1/COUNTIF(A2:A200,A2:A200)))

entered with ctrl + shift & enter

...

Or

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

which needn't be entered as an array formula.

 

[Peo Sjoblom]

Or

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

which needn't be entered as an array formula.

--

Note that if you use Excel prior to 2003 and the range is not fully used it
will
return a #DIV/0! error

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Harlan Grove]

Note that if you use Excel prior to 2003 and the range is not fully used it
will return a #DIV/0! error

Care is needed when critiquing my responses.

I'm running XL97 SR-2, and if I fill A1:A20 with

{1;2;3;4;5;6;7;8;9;10;9;8;7;6;5;4;3;2;1;<blank>}

the formula *above* returns 10, not #DIV/0!. Now if I had foolishly used just

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

(or foolishly failed to notice that I had used the former, not the latter,
formula), then I'd get #DIV/0!. But I know better. The key is coercing the 2nd
arg to COUNTIF to be strings, thus A1:A20&"", which makes the A20 entry ""
rather than 0. Note: if A1 were 0 and A2 blank, COUNTIF(A1,A2) returns 1 because
COUNTIF coerces blank ranges in its 2nd argument to 0.

If you don't believe me, test it in an earlier version.

 

[Peo Sjoblom]

...

Care is needed when critiquing my responses.

I'm running XL97 SR-2, and if I fill A1:A20 with

{1;2;3;4;5;6;7;8;9;10;9;8;7;6;5;4;3;2;1;<blank>}

the formula *above* returns 10, not #DIV/0!. Now if I had foolishly used just

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

(or foolishly failed to notice that I had used the former, not the latter,
formula), then I'd get #DIV/0!. But I know better. The key is coercing the 2nd
arg to COUNTIF to be strings, thus A1:A20&"", which makes the A20 entry ""
rather than 0. Note: if A1 were 0 and A2 blank, COUNTIF(A1,A2) returns 1 because
COUNTIF coerces blank ranges in its 2nd argument to 0.

If you don't believe me, test it in an earlier version.

With {1;2;3;1;2;3} in A1:A6 rest are blank and NEVER used

=SUM(IF(A1:A20<>"",1/COUNTIF(A1:A20,A1:A20)))

returns 3

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

returns

#DIV/0!

tested in excel 2000 and 2002


--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Bob Phillips]

Sorry Peo, but it works for me in Excel 2000.

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

that is the working version.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

...
...

...
With {1;2;3;1;2;3} in A1:A6 rest are blank and NEVER used

=SUM(IF(A1:A20<>"",1/COUNTIF(A1:A20,A1:A20)))

returns 3

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

returns

#DIV/0!

tested in excel 2000 and 2002

...

Oh, that bug. Fine, use

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

Longer than the SUM(IF(...)) formula, but uses one fewer nested function call
level. Your earlier caveat was ambiguous. 'Never used' is clearer than 'not
fully used'.

 

[Bob Phillips]

Would you care to clarify that distinction for me, as my test worked?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

Sorry Peo, but it works for me in Excel 2000.

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

that is the working version.

...

The bug in question is the implicit intersection of COUNTIF/SUMIF 1st argument
with that argument's parent worksheet's used range. If you create a new
workbook, enter 1, 2 and 3 in cells A1:A3, then immediately enter the formula
=COUNTIF(A1:A20,"") in B1, it'll return 0 rather than 17. If you enter the
formula

=COUNTIF(A1:A20,A1:A20&"")

in B1, it'll evaluate to {1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}. Enter
anything in A21, and it'll evaluate to {1;1;1;17;17;17;17;17;17;17;17;17;
17;17;17;17;17;17;17;17}.

 

[Stephen Dunn]

...
..
..

Oh, that bug. Fine, use

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

Longer than the SUM(IF(...)) formula, but uses one fewer nested function call
level. Your earlier caveat was ambiguous. 'Never used' is clearer than 'not
fully used'.

In which case, is &"" still required?

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

Picky, I know.