Count unique text values with criteria

To count unique text values in a range with criteria, you can use an array formula based on the FREQUENCY and MATCH functions. In the example shown, the formula in G6 is: {=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0))} The result is 3 since three different people worked on the Omega project. Note: this is an array formula and must be entered with control + shift + enter in Excel 2019 and earlier. With Excel 365, you can use a much simpler formula based on the UNIQUE function.

April 14, 2025 · 1 min · 104 words · Katie Martin

Table of Contents

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

Note: I adapted the formulas above fromMike Girvin’sexcellent book on array formulas,Control-Shift-Enter.

These formulas can be adapted toapply logical criteria.

Excel formula: Count unique numeric values with criteria

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 numeric values in a range

SUM Function

The Excel SUM function returns the sum of values supplied.

These values can be numbers, cell references, ranges, arrays, and constants, in any combination.

SUM can handle up to 255 individual arguments.

Excel formula: Count unique values in a range with COUNTIF

Excel formula: Count unique text values in a range

Excel formula: Extract unique items from a list

Excel FREQUENCY function

Excel MATCH function

Excel ROW function

Excel SUM function