Unique cell values are greyed, in the picture above.

Array formula in D14:

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

Array formula in D26:

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

Array formula in D38:

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

Array formula in E50:

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

Array formula in E62:

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

Array formula in E74:

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

Related posts:

Filter unique text values from a range containing both numerical and text values in excel

Create a unique distinct text list from a range containing both numerical and text values in excel

Extract and sort text cells from a range containing both numerical and text values

Extract duplicate text values from a range containing both numerical and text values in excel

Count unique distinct text values in a range in excel