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

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

one way:

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

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

Watercolor artist wrote:

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

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!
==============================================



"Watercolor artist" wrote in
message ...
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

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


"Dave Peterson" wrote:

one way:

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

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

Watercolor artist wrote:

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

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(D1:D4)

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

"BorisS" wrote in message
...
can you quickly explain what this is doing? Just curious what the
formula's
logic is.
--
Boris


"Dave Peterson" wrote:

one way:

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

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

Watercolor artist wrote:

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

Thanks Dave. It does exactly what I need.

Howard

"Dave Peterson" wrote:

one way:

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

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

Watercolor artist wrote:

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