ThisCalculator

Everyday

Count Unique Values with Criteria in Excel

Updated · Standard formula, shown below

The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.

This calculator counts the number of unique values in a list that meet a specific condition, such as counting distinct products sold in a particular region or unique customers who made a purchase over $100. You enter your data range, the criteria range, and the condition, and the calculator returns the count of unique matching entries. The result is a single number, ready to use in reports or dashboards.

Formula

The calculator uses the classic SUMPRODUCT and COUNTIFS combination. In words, it counts each row where the criteria match and where the data value appears only once in the entire data range. The formula is:

Unique count with criteria = SUMPRODUCT((criteria_range = criteria) / COUNTIFS(data_range, data_range, criteria_range, criteria))

Variables:

  • data_range – the list of values you want to count unique entries from (e.g., product names, customer IDs).
  • criteria_range – the range that contains the condition you are testing (e.g., region, category).
  • criteria – the specific value you want to match in the criteria range (e.g., “West”).

The result is a count (a whole number) of distinct values in the data range that appear in rows where the criteria range equals the criteria.

Worked Example

Using the default values: data range A2:A100 (assume it contains product names), criteria range B2:B100 (regions), and criteria “West”. Let’s say the data has 10 rows:

  • Row 2: Product X, West
  • Row 3: Product Y, East
  • Row 4: Product X, West
  • Row 5: Product Z, West
  • Row 6: Product X, East
  • Row 7: Product Y, West
  • Row 8: Product Z, East
  • Row 9: Product X, West
  • Row 10: Product W, West

Rows that match “West”: 2, 4, 5, 7, 9, 10. The data values in those rows are X, Z, X, Y, X, W. Unique values among these: X, Z, Y, W – that’s 4. The formula counts each matching row with a fractional weight: for each value, it divides 1 by the number of times that value appears among the matching rows. X appears 3 times, so each X row contributes 1/3; Z, Y, W each appear once, so they contribute 1 each. Sum: 3*(1/3) + 1 + 1 + 1 = 4. The calculator returns 4.

When to Use This Count

This calculation is useful in sales analysis, inventory tracking, and customer behavior studies. For example, you might want to know how many distinct products were sold in a specific region, or how many unique customers purchased a particular item. It is also used in quality control to count distinct defect types per shift.

Common Mistakes and How to Avoid Them

  • Mismatched range sizes: The data range and criteria range must have the same number of rows. If they differ, the formula returns an error or wrong result.
  • Criteria with text: If your criteria is text, enclose it in quotes (e.g., “West”). For numbers, no quotes are needed.
  • Blank cells: Blank cells in the data range are counted as a unique value (blank) if they meet the criteria. To exclude blanks, add a condition like data_range <> “” to the formula.
  • Leading/trailing spaces: Extra spaces in criteria or data cause mismatches. Use TRIM() to clean data.
  • Using COUNTIF instead of COUNTIFS: COUNTIF only handles one condition; you need COUNTIFS to match both the data and the criteria.

Limitations of the Formula

The SUMPRODUCT/COUNTIFS approach works well for moderate data sizes (up to a few thousand rows). For very large datasets (hundreds of thousands of rows), it can become slow. It also assumes that the data range does not contain errors (like #N/A) because COUNTIFS will propagate errors. If your data contains errors, you may need to clean it first. Additionally, the formula counts text and numbers as distinct; for example, the number 5 and the text “5” are considered different values.

Alternatives and Advanced Methods

If you use Excel 365 or Excel 2021, you can use the dynamic array function UNIQUE combined with FILTER. The formula would be:

=COUNTA(UNIQUE(FILTER(data_range, criteria_range = criteria)))

This is more efficient and easier to read. For older Excel versions, the SUMPRODUCT method is the standard. In Google Sheets, you can use COUNTUNIQUEIFS, which directly counts unique values based on multiple criteria. For very large data, a pivot table with a distinct count (available in Excel 2013 and later) is often the fastest approach.

Frequently Asked Questions

How do I count unique values with multiple criteria?

You can extend the formula by adding more criteria ranges and criteria. For example, to count unique products sold in the West region and in the Electronics category, use: =SUMPRODUCT((B2:B100=”West”)*(C2:C100=”Electronics”)/COUNTIFS(A2:A100,A2:A100,B2:B100,”West”,C2:C100,”Electronics”)). The same logic applies: each unique value gets a fractional weight based on how many times it appears among the rows that meet all criteria.

Why does my formula return a decimal or error?

A decimal result usually indicates that some values appear multiple times, and the sum of fractions is not a whole number due to a mismatch in range sizes or a criteria that doesn’t match any rows. An error (#DIV/0!) occurs when a value in the data range appears zero times among the matching rows – this can happen if you have extra rows in the data range that aren’t in the criteria range. Ensure both ranges are exactly the same size and that you have no blank cells in the criteria range.

Can I count unique values based on a condition in another column?

Yes, that’s exactly what this formula does. The criteria range is a separate column, and the data range is the column with the values you want to count unique entries from. For example, count unique customer IDs (data) where the purchase amount (criteria) is greater than $100. You would set the criteria as “>100” and the criteria range as the amount column.

How do I count unique values ignoring blanks?

Add a condition to exclude blanks in the data range. The formula becomes: =SUMPRODUCT((criteria_range=criteria)*(data_range<>“”)/COUNTIFS(data_range,data_range,criteria_range,criteria)). This ensures that blank cells in the data range are not counted as a unique value.

Is there a faster way to count unique values with criteria in Excel?

For large datasets, use the UNIQUE and FILTER functions in Excel 365/2021, or a pivot table with “Add this data to the Data Model” to enable distinct count. In Google Sheets, use COUNTUNIQUEIFS. These methods are more efficient than SUMPRODUCT for large data.

Related

Formula used
in data | Data range | - | A2:A100 | 0
in crit_range | Criteria range | - | B2:B100 | 0
in crit | Criteria | - | "West" | 0
out unique_count | Unique count with criteria | - | count_unique_if(data, crit_range, crit) | 0

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.