Author: Oscar Cronquist Article last updated on October 08, 2018

Count unique distinct text values in a range in excel

Array formula in D14:

=SUM(IF(FREQUENCY(COUNTIF(tbl,  "<"&IF(ISTEXT(tbl), tbl, "")),  COUNTIF(tbl,  "<"&IF(ISTEXT(tbl), tbl, "")))>0,  1,  0)) + CTRL + SHIFT + ENTER

Alternative array formula:

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

Array formula in D15:

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

Named ranges
tbl (B5:F11)
What is named ranges?

How to customize the formula to your excel spreadsheet
Change the named range.

Download excel example file.
Count unique distinct text values in a range.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

Counts the number of cells within a range that meet the given condition

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

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

Adds all the numbers in a range of cells

FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array.