which returns 3, since there are three unique names in B6:B15 associated with Omega. Note: this formula requires Dynamic Array Formulas, available only in Excel 365. With an older version of Excel, you can use more complex alternative formulas. 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: 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: Next, the UNIQUE function is used to remove duplicates: which results in a new array like this: 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 makes things clear, we’ll first rewrite the formula to include the unique list: The LEN function gets the length of each item in the list, and returns an array of lengths: Next, we check if lengths are greater than zero: And use a double negative to coerce the TRUE and FALSE values to 1s and 0s: Finally, we add up the results with the SUM function: This array is delivered directly to the COUNTA function, which returns a final count of 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: 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 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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
