A counting formula that won't count multiple instances of same val

[Guest]

I need a formula that will count all the different instances of a value in a
column. For example, in the example below, the count would be 3 because, "1,"
though it occurs thrice, will only be counted once.

Col A
1
1
2
3
1

Thanks in advance,
Howard

 

[Dave Peterson]

one way:

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

(adjust the range to match, but don't use the whole column)

 

[RagDyer]

Try this:

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

This should work even though you might have blank cells in the range.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Guest]

can you quickly explain what this is doing? Just curious what the formula's
logic is.

 

[Biff]

Hi!

Try this quick and easy experiment....

Enter these values and formulas in the designated cells:

A1 = 20
A2 = 20
A3 = 1
A4 = 2

Enter this formula in B1 and copy down to B4:

=(A1<>"")*1

Enter this formula in C1 and copy down to C4:

=COUNTIF(A$1:A$4,A1)

Enter this formula in D1 and copy down to D4:

=B1/C1

And finally:

=SUM(D14)

Notice the &"" in the Countif function:

COUNTIF(A1:A4,A1:A4&"")

What that does is during the calculation process it appends a null string to
the array of values in A1:A4. If there was an empty cell in that range
without the &"" the formula would return a division error. Try clearing one
of the cells, A1:A4, and see what happens. So, if a cell is empty, the null
string gets "placed" in that element of the array to prevent the error from
occuring.

Biff

 

[Guest]

Thanks Dave. It does exactly what I need.

Howard