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

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...et.f unctions

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" wrote in message
...
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...et.f unctions

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

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...et.f unctions

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...et.f unctions

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

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)+ISLOG ICAL(A1:A15)/COUNTIF(A1:A15,A1:A15&""))

=SUMPRODUCT((ISNUMBER(A1:A15)+ISTEXT(A1:A15)+ISLOG ICAL(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" wrote in message
...
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" wrote in message
...
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...et.f unctions

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&""))

?

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

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/(Rng0))/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?

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 wrote:
. . .

To count only distinct text including "" in Rng,

=SUMPRODUCT(ISTEXT(Rng)/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?

. . . .

Neither of the above works if the range includes blank cells (as
contrasted with cells that contain the empty string).

Alan Beban