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

The following is a UDF that provides for the seven cases set forth by
Harlan Grove below; watch for word wrap:.

Function COUNTU(InputRange, Criterion)
Rng = InputRange.Address
Select Case Criterion
Case "NonBlankText"
COUNTU =
Evaluate("=SUMPRODUCT(ISTEXT(Rng)*ISNUMBER(1/(Rng""""))/COUNTIF(Rng,Rng&""""))")
Case "PositiveNumbers"
COUNTU =
Evaluate("=SUMPRODUCT(ISNUMBER(Rng)*ISNUMBER(1/(Rng0))/COUNTIF(Rng,Rng&""""))")
Case "NumbersOrText"
COUNTU =
Evaluate("=SUMPRODUCT((ISNUMBER(Rng)+ISTEXT(Rng))/COUNTIF(Rng,Rng&""""))")
Case Else
COUNTU = Evaluate("=SumProduct(" & Criterion & "(" & Rng &
") /" & _
"CountIf(" & Rng & ", " & Rng & "&""""))")
End Select
End Function

The parameters for Criterion are, respectively:

"ISNUMBER"
"ISTEXT"
"NonBlankText"
"ISERROR"
"ISLOGICAL"
"PositiveNumbers"
"NumbersOrText"

As mentioned in an earlier post, the results for "ISTEXT" and
"NumbersOrText" work only if the range does not include blank cells (as
contrasted with cells containing the empty string--i.e.,"")

Alan Beban

Harlan Grove wrote:
Alan Beban wrote...

Harlan Grove wrote:
. . .

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