Counting independent items in a list

This formula is meant to count independent entries in A1:A20.
=ROUND(SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20)),0)

Can anyone make this better? I am not talking about using Absolute
references, but ways to modify it so it works in every case. For instance,
at first I had an empty cell in my range and the #DIV/0 error was returned.
I don't fully understand the two negatives in front of some formulas, but I
am wondering if that would work better, too?
=ROUND(SUMPRODUCT(1/--COUNTIF(A1:A20,A1:A20)),0)

Thanks for any help.
-- Brad E.

Hi,

here's an alternative that ignores blanks

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

the double unary (--) coerce true or false into one or zero but do nothing
in your formula.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Brad E." wrote:

This formula is meant to count independent entries in A1:A20.
=ROUND(SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20)),0)

Can anyone make this better? I am not talking about using Absolute
references, but ways to modify it so it works in every case. For instance,
at first I had an empty cell in my range and the #DIV/0 error was returned.
I don't fully understand the two negatives in front of some formulas, but I
am wondering if that would work better, too?
=ROUND(SUMPRODUCT(1/--COUNTIF(A1:A20,A1:A20)),0)

Thanks for any help.
-- Brad E.

Hi,

I should have added there are other (better) ways to do this and Bernd P has
a well presented web page on how it should be done

http://www.sulprobil.com/html/count_unique.html

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Brad E." wrote:

This formula is meant to count independent entries in A1:A20.
=ROUND(SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20)),0)

Can anyone make this better? I am not talking about using Absolute
references, but ways to modify it so it works in every case. For instance,
at first I had an empty cell in my range and the #DIV/0 error was returned.
I don't fully understand the two negatives in front of some formulas, but I
am wondering if that would work better, too?
=ROUND(SUMPRODUCT(1/--COUNTIF(A1:A20,A1:A20)),0)

Thanks for any help.
-- Brad E.

This is Bernd's webpage that i was looking for where he specifically comments
on the solution I gave you

http://www.sulprobil.com/html/excel_don_ts.html
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Brad E." wrote:

This formula is meant to count independent entries in A1:A20.
=ROUND(SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20)),0)

Can anyone make this better? I am not talking about using Absolute
references, but ways to modify it so it works in every case. For instance,
at first I had an empty cell in my range and the #DIV/0 error was returned.
I don't fully understand the two negatives in front of some formulas, but I
am wondering if that would work better, too?
=ROUND(SUMPRODUCT(1/--COUNTIF(A1:A20,A1:A20)),0)

Thanks for any help.
-- Brad E.