Summary
To count unique values in a set of data, you can use the UNIQUE function together with the COUNTA function. In the example shown, the formula in F5 is:
=COUNTA(UNIQUE(B5:B16))
which returns 7, since there are seven unique colors in B5:B16.
Generic formula
=COUNTA(UNIQUE(data))
Explanation
This example uses the UNIQUE function to extract unique values. When UNIQUE is provided with the range B5:B16, which contains 12 values, it returns the 7 unique values seen in D5:D11. These are returned directly to the COUNTA function as an array like this:
=COUNTA({"red";"amber";"green";"blue";"purple";"pink";"gray"})
Unlike the COUNT function, which counts only numbers, COUNTA counts both text and numbers. Since there are seven items in array, COUNTA returns 7. This formula is dynamic and will recalculate immediately when source data is changed.
With a cell reference
You can also refer to a list of unique values already extracted to the worksheet with the UNIQUE function using a special kind of cell reference. The formula in D5 is:
=UNIQUE(B5:B16)
Which returns the seven values seen in D5:D11. To count these values with a dynamic reference, you can use a formula like this:
=COUNTA(D5#)
The hash character (#) tells Excel to refer to the spill range created by UNIQUE. Like the all-in-one formula above, this formula is dynamic and will adapt when data is added or removed from the original range.
Count unique and ignore blank cells
If blank (i.e., empty) cells exist in the range, they can increase the count by 1. The problem is that the empty cells evaluate to zero (0), and UNIQUE returns the zero along with other unique values. Then COUNTA counts the zero. To count unique values while ignoring blank cells, you can add the FILTER function like this:
=COUNTA(UNIQUE(FILTER(data,data<>"")))
Here, FILTER is configured to select only non-empty cells, so empty cells are excluded and never make it to the UNIQUE function. This approach is explained in more detail here. You can also filter unique values with criteria.
Count unique numbers or text
To count only the unique numbers in a range that holds both numbers and text, use the COUNT function instead of COUNTA, since COUNT ignores text. To count only the unique text values, test the results with the ISTEXT function and add them up. In the worksheet below, B5:B16 holds a mix of colors and numbers. The formulas in D5 and E5 are:
=COUNT(UNIQUE(B5:B16)) // unique numbers
=SUM(--ISTEXT(UNIQUE(B5:B16))) // unique text
UNIQUE returns the 7 unique values:
{"red";100;"blue";200;"green";300;"purple"}
COUNT counts the 3 numbers. ISTEXT returns TRUE for the 4 text values, the double negative (--) converts TRUE and FALSE to 1 and 0, and SUM returns 4. Both formulas ignore empty cells. Note that a number stored as text counts as text.
No data
One limitation of the formula above is that it will incorrectly return 1 even if there aren't any values in the data range. This alternative formula will count all values returned by UNIQUE that have a length greater than zero. In other words, it will count all values with at least one character:
=SUM(--(LEN(UNIQUE(B5:B16))>0))
Here, the LEN function is used to check the length of results from UNIQUE. The lengths are then checked to see if they are greater than zero, and results are counted with the SUM function. This is an example of boolean logic. This formula will also exclude empty cells from results.
Dynamic source range
UNIQUE won't automatically change the source range if data is added or deleted. To give UNIQUE a dynamic range that will automatically resize as needed, you can use an Excel Table, or create a dynamic named range with a formula.
Excel 2019 and earlier
Legacy Excel has no UNIQUE function. To count unique values in an older version, see Count unique values in a range with COUNTIF, which works in every version of Excel.
Dynamic Array Formulas are available in Excel 2021+ and Excel 365.
