B
Bob
I need to count the number of unique cell values in a
column, any ideas how to accomplish this?
column, any ideas how to accomplish this?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
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)
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)
P
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)
=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)
K
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
=SUMPRODUCT(1/COUNTIF($A$1:$A$100,$A$1:$A$100))
For more detail and options:-
http://www.cpearson.com/excel/duplicat.htm#CountingUnique
H
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.
P
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)
H
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.
P
Peo Sjoblom
Harlan Grove said:...
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)
B
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)
=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)
H
Harlan Grove
...
...
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'.
...
......
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'.
B
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)
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
H
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}.
S
Stephen Dunn
Harlan Grove said:...
..
..
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.