Count unique values with criteria

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

This array is delivered directly to the COUNTA function, which returns a final count of 3:

=COUNTA({"Jim";"Carl";"Sue"}) // 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.