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

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" <[email protected]> wrote in
message news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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
>

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" <[email protected]> wrote in
message news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>>

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
>

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" <[email protected]> wrote in
message news:[email protected]...
> 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

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
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>>

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
>

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" <[email protected]> wrote in
message news:[email protected]...
> 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

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