How to Count Unique Values Based on Multiple Criteria in Excel

To count unique values based on multiple criteria in Excel, you can use a combination of the SUM, IF, and FREQUENCY functions. Here's a step-by-step guide on how to do this:

1. Organize your data in a table with headers.
2. Identify the columns where you want to apply the criteria and the range of cells that you want to count unique values from.
3. Create a helper column to combine the criteria from the columns you want to use for counting unique values.
4. Use the SUM, IF, and FREQUENCY functions to count unique values based on the criteria in the helper column.

Here's an example:

Example

Let's say you have a dataset of sales data with the following headers: Product, Region, and Sales.

You want to count the unique products sold in the East region. Here's how to do this:

1. Add a helper column to combine the Product and Region columns. In cell D1, add the header "Criteria," and in cell D2, enter the formula =A2&B2. Copy this formula down to the rest of the cells in the Criteria column.

2. Use the SUM, IF, and FREQUENCY functions in a new cell to count the unique values based on the criteria in the helper column. In this example, we want to count unique products sold in the East region, so we only consider cells in the Criteria column with an "E" at the end.

Enter the following formula in a new cell:

=SUM(1/COUNTIFS(D2:D7,D2:D7,RIGHT(D2:D7,1),"E"))

3. Press Ctrl + Shift + Enter to enter this formula as an array formula. Excel will surround the formula with curly braces {} to indicate it's an array formula.

The result will be 3, which is the count of unique products sold in the East region.