Counting unique values
Bob
column, any ideas how to accomplish this?
Bob Phillips
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)
"Bob" <rds...@qwest.net> wrote in message
news:1f67901c457c1$ed3f4db0$a501...@phx.gbl...
Peo Sjoblom
=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)
"Bob" <rds...@qwest.net> wrote in message
news:1f67901c457c1$ed3f4db0$a501...@phx.gbl...
Ken Wright
=SUMPRODUCT(1/COUNTIF($A$1:$A$100,$A$1:$A$100))
For more detail and options:-
http://www.cpearson.com/excel/duplicat.htm#CountingUnique
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
"Bob" <rds...@qwest.net> wrote in message
news:1f67901c457c1$ed3f4db0$a501...@phx.gbl...
> I need to count the number of unique cell values in a
> column, any ideas how to accomplish this?
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.708 / Virus Database: 464 - Release Date: 18/06/2004
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.
--
To top-post is human, to bottom-post and snip is sublime.
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
Harlan Grove
>>Or
>>
>>=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))
>>
>>which needn't be entered as an array formula.
>
>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
news:6HHBc.6527$H4....@www.newsranger.com...
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
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
>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
=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)
"Peo Sjoblom" <ter...@mvp.org> wrote in message
news:O5uRyE9V...@TK2MSFTNGP09.phx.gbl...
Bob Phillips
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Harlan Grove" <hrl...@aol.com> wrote in message
news:AhIBc.6532$H4...@www.newsranger.com...
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
In which case, is &"" still required?
=SUMPRODUCT((A1:A20<>"")/(COUNTIF(A1:A20,A1:A20)+(A1:A20="")))
Picky, I know.