Count unique text values in a range

To count unique text values in a range, you can use a formula based on several functions: FREQUENCY, MATCH, ROW, and SUMPRODUCT. In the example shown, the formula in F5 is: =SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0)) which returns 4, since there are 4 unique names in B5:B14. Note: Another way to count unique values is to use the COUNTIF function. This is a much simpler formula, but it can run slowly on large data sets. In Dynamic Excel, you can use a simpler and faster formula based on UNIQUE.

April 14, 2025 · 1 min · 108 words · Jonathon Payne

Table of Contents

This array is fed into FREQUENCY as thedata_arrayargument.

When a number has already been counted, FREQUENCY will return zero.

This is a key feature in the operation of this formula.

Excel formula: Count unique numeric values in a range

For more information,see this page.

These formulas can be adapted toapply logical criteria.

A pivot table is also an excellent way tolist and count unique values.

Excel formula: Count unique values in a range with COUNTIF

MATCH supports approximate and exact matching, andwildcards(* ?)

For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet.

When no reference is provided, ROW returns the row number of the cell which contains the formula.

Excel formula: Count unique text values with criteria

SUMPRODUCT Function

The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products.

Excel formula: Extract unique items from a list

Excel FREQUENCY function

Excel MATCH function

Excel ROW function

Excel SUMPRODUCT function