S
SB
Does anyone know if there is a formula for count
distinct? I can't seem to find anything in the help on
it.
Thanks.
distinct? I can't seem to find anything in the help on
it.
Thanks.
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.
J
Jason Morin
One way:
=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))
HTH
Jason
Atlanta, GA
=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))
HTH
Jason
Atlanta, GA
F
Frank Kabel
Hi
try
=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
try
=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
B
brendon
Jason, I try using the formula, but all I get is the
#DIV/0!. Am I doing something wrong.
#DIV/0!. Am I doing something wrong.
H
Harlan Grove
....brendon said:Jason, I try using the formula, but all I get is the
#DIV/0!. Am I doing something wrong.
You likely have some blank cells in the range. If so, use either of the
following instead.
=SUMPRODUCT((A1:A100<>"")/(COUNTIF(A1:A100,A1:A100)+(A1:A100=""))
=SUMPRODUCT((1-ISBLANK(A1:A100))/(COUNTIF(A1:A100,A1:A100)
+ISBLANK(A1:A100)))
The first excludes blank cells and cells evaluating to "". The second
excludes only blank cells.