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, the 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 .
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.
No dynamic arrays
If you are using an older version of Excel without dynamic array support, here are some alternatives .
Dynamic Array Formulas are new in Excel.
Explanation
In this example, the goal is to count unique values that meet one or more specific conditions. In the example shown, the formula used in cell H7 is:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
At the core, this formula uses the FILTER function to apply criteria, and the UNIQUE function to extract the unique values that remain. Working from the inside out, the FILTER function is used to apply criteria and extract only names that are associated with the “Omega” project:
FILTER(B6:B15,C6:C15=H6,"") // Omega names only
Notice the if_empty argument in FILTER is set to an empty string (""), which is important due to the way we count final results. The result from FILTER is an array like this:
{"Jim";"Jim";"Carl";"Sue";"Carl"}
Next, the UNIQUE function is used to remove duplicates:
UNIQUE({"Jim";"Jim";"Carl";"Sue";"Carl"})
which results in a new array like this:
{"Jim";"Carl";"Sue"} // after UNIQUE
At this point, we have a unique list of names associated with Omega, and we just need to count them. For reasons explained below, we do this with the LEN function and the SUM function. To make things clear, we’ll first rewrite the formula to include the unique list:
=SUM(--(LEN({"Jim";"Carl";"Sue"})>0))
The LEN function gets the length of each item in the list, and returns an array of lengths:
LEN({"Jim";"Carl";"Sue"}) // returns {3;4;3}
Next, we check if lengths are greater than zero:
LEN({3;4;3)>0 // returns {TRUE;TRUE;TRUE}
And use a double negative to coerce the TRUE and FALSE values to 1s and 0s:
--({TRUE;TRUE;TRUE}) // returns {1;1;1}
Finally, we add up the results with the SUM function :
=SUM({1;1;1}) // returns 3
Note that because we are checking the length of each item returned by UNIQUE, empty cells that meet criteria are ignored. Likewise, if FILTER returns an empty string (""), which has a length of zero, it will not be included in the count.
This formula is dynamic and will recalculate immediately if source data is changed.
Count unique with multiple criteria
To count unique values based on multiple criteria, can extend the “include” logic inside FILTER. For example, to count unique names for the Omega project in June only, use:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"),"")))>0))
This is an example of using boolean logic to apply more than one condition. The approach is explained in more detail here .
For more details, see this training video: How to filter with multiple criteria .
COUNTA
It is possible to write a simpler formula that relies on the COUNTA function . However, an important caveat is that COUNTA will return 1 when there are no matching values. This is because the FILTER function returns an error when no data matches criteria, and this error ends up being counted by the COUNTA function. The basic COUNTA formula looks like this:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Again, this formula will return 1 when there is no matching data. It will also include empty cells that meet criteria. The formula based on LEN and SUM is a better option.
No dynamic arrays
If you are using an older version of Excel without dynamic array support, you can use a more complex formula . For a more general discussion of dynamic array alternatives, see Alternatives to Dynamic Array Formulas .
Dynamic Array Formulas are new in Excel.
