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

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?

Change the named range.

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

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

SUM(number1,[number2],)
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.