Count Unique Values

 

This example shows you how to create an array formula that counts unique values.

1. We use the COUNTIF function. For example, to count the number of 5's, use the following function.

Countif Function

2. To count the unique values (don't be overwhelmed), we add the SUM function, 1/, and replace 5 with A1:A6.

Array Formula

3. Finish by pressing CTRL + SHIFT + ENTER.

Count Unique Values Result

Note: The formula bar indicates that this is an array formula by enclosing it in curly braces {}. Do not type these yourself. They will disappear when you edit the formula.

Explanation: The range (array constant) created by the COUNTIF function is stored in Excel's memory, not in an range. The array constant looks as follows:

{3;1;1;1;3;3} - (three 7's, one sun, one moon, one 5, three 7's, three 7's)

This reduces to:
{1/3;1/1;1/1;1/1;1/3;1/3}

This array constant is used as an argument for the SUM function, giving a result of 1/3+1+1+1+1/3+1/3 = 4.