"COUNTU" function in Excel to count unique entries in a range

[WayneL]

In order to count the number of unique entries in a range of cells, a very
complicated nested set of functions (SUM, IF, FREQUENCY, MATCH, LEN, etc.) is
required in Excel.

I suggest adding a COUNTU worksheet function that would automatically count
the number of unique data entries. It should have options for counting
numbers, numbers + text, excluding blank cells, etc.

Thanks,
Wayne

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

[Biff]

Hi!

> In order to count the number of unique entries in a range of cells, a very
> complicated nested set of functions (SUM, IF, FREQUENCY, MATCH, LEN, etc.)
> is
> required in Excel.

Not really!

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

Biff

"WayneL" <WayneL@discussions.microsoft.com> wrote in message
news:735EE93F-CCF4-41BC-9D5B-553A4CC72043@microsoft.com...
> In order to count the number of unique entries in a range of cells, a very
> complicated nested set of functions (SUM, IF, FREQUENCY, MATCH, LEN, etc.)
> is
> required in Excel.
>
> I suggest adding a COUNTU worksheet function that would automatically
> count
> the number of unique data entries. It should have options for counting
> numbers, numbers + text, excluding blank cells, etc.
>
> Thanks,
> Wayne
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow
> this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...heet.functions

[Alan Beban]

Or, if the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook

=ArrayCount(ArrayUniques(a1:a15))

Alan Beban

Biff wrote:
> Hi!
>
>
>>In order to count the number of unique entries in a range of cells, a very
>>complicated nested set of functions (SUM, IF, FREQUENCY, MATCH, LEN, etc.)
>>is
>>required in Excel.
>
>
> Not really!
>
> =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&""))
>
> Biff
>
> "WayneL" <WayneL@discussions.microsoft.com> wrote in message
> news:735EE93F-CCF4-41BC-9D5B-553A4CC72043@microsoft.com...
>
>>In order to count the number of unique entries in a range of cells, a very
>>complicated nested set of functions (SUM, IF, FREQUENCY, MATCH, LEN, etc.)
>>is
>>required in Excel.
>>
>>I suggest adding a COUNTU worksheet function that would automatically
>>count
>>the number of unique data entries. It should have options for counting
>>numbers, numbers + text, excluding blank cells, etc.
>>
>>Thanks,
>>Wayne
>>
>>----------------
>>This post is a suggestion for Microsoft, and Microsoft responds to the
>>suggestions with the most votes. To vote for this suggestion, click the "I
>>Agree" button in the message pane. If you do not see the button, follow
>>this
>>link to open the suggestion in the Microsoft Web-based Newsreader and then
>>click "I Agree" in the message pane.
>>
>>http://www.microsoft.com/office/comm...heet.functions
>
>
>

[Harlan Grove]

Alan Beban wrote...
>Or, if the functions in the freely downloadable file at
>http:/home.pacbell.net/beban are available to your workbook
>
>=ArrayCount(ArrayUniques(a1:a15))
....

Someone's got to ask, why use ArrayCount rather than the
built-in COUNTA function, as in

=COUNTA(ArrayUniques(A1:A15))

?

But more to the point, if all one wants to do is *COUNT*
the distinct entries in a range, why would one ever want
to use a slow udf rather than the much faster

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

?

[Alan Beban]

Harlan Grove wrote:
> Alan Beban wrote...
>
>>Or, if the functions in the freely downloadable file at
>>http:/home.pacbell.net/beban are available to your workbook
>>
>>=ArrayCount(ArrayUniques(a1:a15))
>
> ...
>
> Someone's got to ask, why use ArrayCount rather than the
> built-in COUNTA function, as in
>
> =COUNTA(ArrayUniques(A1:A15))
>
> ?
>
> But more to the point, if all one wants to do is *COUNT*
> the distinct entries in a range, why would one ever want
> to use a slow udf rather than the much faster
>
> =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&""))
>
> ?
>
None of the above suggestions deals with *all* of the OP's specs, which
Harlan Grove omitted in his post.

"It should have options for counting
numbers, numbers + text, excluding blank cells, etc."

For whatever it may be worth

=ArrayCount(ArrayUniques(a1:a15)) returns the number of unique entries
excluding blanks; =ArrayCount(ArrayUniques(a1:a15,,,False) returns the
number of unique entries including blanks.

=COUNTA(ArrayUniques(A1:A15)and =COUNTA(ArrayUniques(A1:A15),,,False)
both return the number of unique entries excluding blanks.

Additional suggestions would be required to deal with *all* the OP's specs.

Alan Beban

[Harlan Grove]

Alan Beban wrote...
>Harlan Grove wrote:
....
>>But more to the point, if all one wants to do is *COUNT*
>>the distinct entries in a range, why would one ever want
>>to use a slow udf rather than the much faster
>>
>>=SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&""))
>>
>> ?
>
>None of the above suggestions deals with *all* of the OP's specs, which
>Harlan Grove omitted in his post.
>
>"It should have options for counting numbers, numbers + text, excluding
>blank cells, etc."
>
>For whatever it may be worth
>
>=ArrayCount(ArrayUniques(a1:a15)) returns the number of unique entries
>excluding blanks; =ArrayCount(ArrayUniques(a1:a15,,,False) returns the
>number of unique entries including blanks.

Missing a right parenthesis on that second formula.

>=COUNTA(ArrayUniques(A1:A15)and =COUNTA(ArrayUniques(A1:A15),,,False)
>both return the number of unique entries excluding blanks.
....

Missing another right parenthesis on the first formula and looks like a
prematute right parenthesis in the second formula.

Fine. =ROWS(ArrayUniques(A1:A15)) returns the distinct count excluding
blanks, and =ROWS(ArrayUniques(A1:A15,,,FALSE)) returns the distinct
count including blanks.

To count only distinct numbers in Rng,

=SUMPRODUCT(ISNUMBER(Rng)/COUNTIF(Rng,Rng&""))


To count only distinct text including "" in Rng,

=SUMPRODUCT(ISTEXT(Rng)/COUNTIF(Rng,Rng&""))


To count only distinct text excluding "" in Rng,

=SUMPRODUCT(ISTEXT(Rng)*ISNUMBER(1/(Rng<>""))/COUNTIF(Rng,Rng&""))


To count distinct error values in Rng,

=SUMPRODUCT(ISERROR(Rng)/COUNTIF(Rng,Rng&""))


To count distinct logical values in Rng,

=SUMPRODUCT(ISLOGICAL(Rng)/COUNTIF(Rng,Rng&""))


To count distinct positive numbers in range,

=SUMPRODUCT(ISNUMBER(Rng)*ISNUMBER(1/(Rng>0))/COUNTIF(Rng,Rng&""))


To count distinct numbers or text in Rng,

=SUMPRODUCT((ISNUMBER(Rng)+ISTEXT(Rng))/COUNTIF(Rng,Rng&""))


Are we getting the point yet?


Since ArrayUniques has the function definition

Function ArrayUniques(InputArray, _
Optional MatchCase As Boolean = True, _
Optional Base_Orient As String = "1vert", _
Optional OmitBlanks As Boolean = True)

I can see how it could include or exclude blanks, but how would one
need to call it to count distinct numbers or text in ranges that could
also include booleans and errors?

[Alan Beban]

Harlan Grove wrote:
> . . .
> But more to the point, if all one wants to do is *COUNT*
> the distinct entries in a range, why would one ever want
> to use a slow udf rather than the much faster
>
> =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&""))
>
> ?
>
Because the range may contain error values.

Alan Beban

[Harlan Grove]

"Alan Beban" <unavailable@no.com> wrote...
>Harlan Grove wrote:
>> . . .
>>But more to the point, if all one wants to do is *COUNT*
>>the distinct entries in a range, why would one ever want
>>to use a slow udf rather than the much faster
>>
>>=SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&""))
>>
>>?
>
>Because the range may contain error values.

Oh so clever to add this after I considered the possibility in an earlier
response. Also, to deal with the nastier problem of both zero length strings
and truly blank cells in the range as well as error values, a strictly
non-udf formula is still possible, like the following array formula.

=SUM(1/MMULT(--(IF(ISNUMBER(1/(x<>"")),x)
=TRANSPOSE(IF(ISNUMBER(1/(x<>"")),x))),ROW(x)^0))-1

[KL]

Hi,

I also use these versions of the formula posted by Biff:

=SUMPRODUCT((ISNUMBER(A1:A15)+ISTEXT(A1:A15))/COUNTIF(A1:A15,A1:A15&""))

=SUMPRODUCT((ISNUMBER(A1:A15)+ISTEXT(A1:A15)+ISLOGICAL(A1:A15)/COUNTIF(A1:A15,A1:A15&""))

=SUMPRODUCT((ISNUMBER(A1:A15)+ISTEXT(A1:A15)+ISLOGICAL(A1:A15)+ISERROR(A1:A15))/COUNTIF(A1:A15,A1:A15&""))

which give a lot of flexibility in what to count and are "error-tolerant".
You just take out the types you don't want to be counted.

I couldn't however figure out how to combine 'error-tolerance' and counting
in the "blank" cells as there may be "" returned by formulas which are not
recognised by ISBLANK function.

I do recognize they may become a bit long, but they don't seem to be too
complex and again they are quite flexible.

Regards,
KL



"Biff" <biffinpitt@comcast.net> wrote in message
news:eBUmRowZFHA.1404@TK2MSFTNGP09.phx.gbl...
> Hi!
>
>> In order to count the number of unique entries in a range of cells, a
>> very
>> complicated nested set of functions (SUM, IF, FREQUENCY, MATCH, LEN,
>> etc.) is
>> required in Excel.
>
> Not really!
>
> =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&""))
>
> Biff
>
> "WayneL" <WayneL@discussions.microsoft.com> wrote in message
> news:735EE93F-CCF4-41BC-9D5B-553A4CC72043@microsoft.com...
>> In order to count the number of unique entries in a range of cells, a
>> very
>> complicated nested set of functions (SUM, IF, FREQUENCY, MATCH, LEN,
>> etc.) is
>> required in Excel.
>>
>> I suggest adding a COUNTU worksheet function that would automatically
>> count
>> the number of unique data entries. It should have options for counting
>> numbers, numbers + text, excluding blank cells, etc.
>>
>> Thanks,
>> Wayne
>>
>> ----------------
>> This post is a suggestion for Microsoft, and Microsoft responds to the
>> suggestions with the most votes. To vote for this suggestion, click the
>> "I
>> Agree" button in the message pane. If you do not see the button, follow
>> this
>> link to open the suggestion in the Microsoft Web-based Newsreader and
>> then
>> click "I Agree" in the message pane.
>>
>> http://www.microsoft.com/office/comm...heet.functions
>
>

[Aladin Akyurek]

WayneL wrote:
> In order to count the number of unique entries in a range of cells, a very
> complicated nested set of functions (SUM, IF, FREQUENCY, MATCH, LEN, etc.) is
> required in Excel.
>
> I suggest adding a COUNTU worksheet function that would automatically count
> the number of unique data entries. It should have options for counting
> numbers, numbers + text, excluding blank cells, etc.
>
> Thanks,
> Wayne
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...heet.functions

A good candidate is Longre's (updated) COUNTDIFF from his morefunc add-in.