Count unique and unique distinct text and numerical values in a range

Unique cell values are greyed, in the picture above.

Array formula in D14:

=SUM(IF(COUNTIF(tbl, tbl)=1, 1, 0)) + CTRL + SHIFT + ENTER

Count unique and unique distinct text and numerical values in a range1

Unique distinct cell values are greyed, in the picture above.

Array formula in D26:

=SUM(IF(ISBLANK(tbl), 0, 1/(COUNTIF(tbl, tbl)))) + CTRL + SHIFT + ENTER

Count unique and unique distinct text and numerical values in a range2

Unique text cell values are greyed, in the picture above.

Array formula in D38:

=SUM(IF(ISTEXT(tbl)*COUNTIF(tbl, tbl)=1, 1/(COUNTIF(tbl, tbl)), 0)) + CTRL + SHIFT + ENTER

Count unique and unique distinct text and numerical values in a range3

Unique distinct text cell values are greyed, in the picture above.

Array formula in E50:

=SUM(IF(ISTEXT(tbl), 1/(COUNTIF(tbl, tbl)), 0)) + CTRL + SHIFT + ENTER

Count unique and unique distinct text and numerical values in a range4

Unique numerical cell values are greyed, in the picture above.

Array formula in E62:

=SUM(IF(ISNUMBER(tbl)*COUNTIF(tbl, tbl)=1, 1/(COUNTIF(tbl, tbl)), 0)) + CTRL + SHIFT + ENTER

Count unique and unique distinct text and numerical values in a range5

Unique distinct numerical cell values are greyed, in the picture above.

Array formula in E74:

=SUM(IF(ISNUMBER(tbl), 1/(COUNTIF(tbl, tbl)), 0)) + CTRL + SHIFT + ENTER

Named ranges

tbl (B6:F12)
What is named ranges?

How to implement array formula to your workbook

Change the named range.

Download excel file for this tutorial.

Count unique text values in a range containing both numerical and text values.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ISTEXT(value)
Checks whether a value is text, and returns TRUE or FALSE

ISNUMBER(value)
Checks whether a value is a number and returns TRUE or FALSE

ISBLANK(value)
Checks whether a reference is to an empty cell and returns TRUE or FALSE

SUM(number1,[number2],)
Adds all the numbers in a range of cells