Count Unique Values in Excel With One or More Criteria

Formulas & functions · Updated November 22, 2026

In Microsoft 365 for Windows, count distinct nonblank values that meet one condition with =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,(B2:B100=E2)*(A2:A100<>"")))),0). This counts the unique entries in A2:A100 only where the corresponding cells in B2:B100 match E2; change the ranges and criterion cell to fit your data.

What the formula counts

Suppose column A contains product names and column B contains sales regions. If the East region has Widget, Cable, Widget, and Charger in its rows, the formula returns 3: Widget is counted once, not twice. It ignores blank product cells, even if their region matches. This is usually what people mean by counting unique values with criteria: count each qualifying value once and exclude empty entries.

The formula is designed for Excel for Microsoft 365 and uses the dynamic-array functions FILTER and UNIQUE, available in Microsoft 365 and Excel 2021. It also uses ROWS to count the resulting list. IFERROR returns zero when no nonblank values satisfy the condition, instead of showing a calculation error.

For the formula above, assume A2:A100 contains products, B2:B100 contains regions, and E2 contains East. In a small example where the qualifying products are Widget, Cable, Widget, and a blank, the result is 2. The blank is excluded, and the repeated Widget contributes only one distinct value.

How the formula works

The expression is built in stages. FILTER first keeps the entries in the product range whose corresponding region equals the criterion. The additional test A2:A100<>"" removes blank products. UNIQUE then reduces the filtered results to distinct values, and ROWS counts how many values remain.

  • B2:B100=E2 checks each region against the requested region.
  • A2:A100<>"" checks that each product cell is not empty.
  • The asterisk between the tests acts as AND: both conditions must be true for a row to pass.
  • IFERROR(...,0) handles the case where FILTER has no qualifying values.

The ranges should have matching dimensions. For example, A2:A100 and B2:B100 each cover 99 rows; if one range ends at row 100 and the other at row 101, FILTER cannot pair the records correctly and may return an error. Whole-column references can also make a formula process far more cells than necessary, so use sensible bounds or an Excel Table for growing datasets.

Set up the data and criterion

Keep the values being counted and the conditions in separate, aligned columns. Put the criterion in a cell such as E2 so it can be changed without editing the formula. For example, A2:A100 might hold product names, B2:B100 the region, and E2 the region to report. Make sure every row refers to the same record; sorting just one column can break that relationship.

For a reusable dataset, convert the range to an Excel Table by selecting a cell in it and choosing Insert > Table. Tables expand as you add records and make formulas easier to read, although structured-reference versions can be less familiar at first. For a one-off analysis, ordinary cell ranges are often simpler. Check that values which look identical do not contain extra spaces or inconsistent spelling, since “Cable” and “Cable ” may be treated as separate entries.

Enter the formula in an empty cell outside the source data. The calculation returns a number, not a list of the matching values. If you also want to inspect the distinct list, use FILTER and UNIQUE without ROWS, keeping the same condition tests and blank exclusion. The list will spill into cells below the formula, so those cells must be empty.

Count unique values with multiple criteria

To require two conditions, multiply the tests inside FILTER. Assume A2:A100 contains products, B2:B100 contains sales teams, C2:C100 contains regions, E2 contains East, and F2 contains North. This counts distinct products only for records that match both selected values:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A100,(B2:B100=E2)*(C2:C100=F2)*(A2:A100<>"")))),0)

If the matching East-and-North rows contain Widget, Cable, Widget, and a blank product, the result is 2. Both conditions must be true, and repeated products are still counted only once. Add another parenthesized comparison and multiply it into the include argument for each additional AND condition.

For an OR condition, add the tests rather than multiplying them, then test whether the sum is greater than zero. Assume A2:A100 contains products, B2:B100 contains regions, E2 contains East, and F2 contains West. The formula counts each nonblank product once if it appears in either region:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A100,(((B2:B100=E2)+(B2:B100=F2))>0)*(A2:A100<>"")))),0)

A product appearing in both regions is still counted once because UNIQUE acts after the rows are filtered. With OR logic, parentheses matter: they make the two comparisons combine into one true-or-false test before the blank check is applied.

Use date, number, and text conditions

Conditions do not have to be exact text matches. You can use comparisons such as greater than, less than, or greater than or equal to inside FILTER. For example, to count distinct products sold on a particular day, use a start date and the following day as the exclusive end. Assume products are in A2:A100, date-and-time stamps are in B2:B100, E2 contains the day to report, and F2 contains the next day:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A100,(B2:B100>=E2)*(B2:B100<F2)*(A2:A100<>"")))),0)

This includes timestamps at any time during E2’s date while excluding midnight at the start of F2. For instance, if the selected day’s qualifying products are P1, P2, P1, and P3, the result is 3. The start and end cells should contain real Excel dates, not text that merely looks like a date.

You can apply similar comparisons to numeric criteria, such as counting distinct customer IDs for orders above a threshold. Text matching in ordinary Excel comparisons is generally case-insensitive, so “North” and “NORTH” are not normally separate criterion values. If case-sensitive matching or data cleaning is important, prepare a helper column or use a more specialized formula rather than assuming the basic comparison distinguishes capitalization.

Common errors and how to fix them

  • An error appears when there are no matches: FILTER returns an error when nothing qualifies unless its optional empty-result argument is supplied. The example formulas use IFERROR to return zero.
  • The count seems too high: Look for trailing spaces, spelling differences, or values stored in different forms. “0012” stored as text may not behave like the number 12, and these can be distinct values.
  • A blank seems to count: Keep the nonblank test on the values being counted, not just on the criteria column. A matching region does not guarantee that its product cell is populated.
  • The formula returns an unexpected zero: Confirm that the criterion cell contains the same text or value as the source cells, that the ranges align, and that the source values are not all blank.
  • The formula shows #SPILL!: A spill formula used to display a list needs clear cells in its output area. A count formula returns a single number, but any separate UNIQUE list can be blocked by existing content.

One subtle distinction is between a truly empty cell and a formula cell that displays an empty string. The test A2:A100<>"" excludes cells that evaluate to an empty string as well as ordinary blanks, which is usually useful for reports. If zero is a valid value, it is not blank and remains eligible to be counted.

Alternatives for older Excel versions and summaries

FILTER and UNIQUE are available in Microsoft 365 and Excel 2021, but not in every older perpetual version of Excel. If your installation does not recognize these functions, a PivotTable can summarize the data without a dynamic-array formula. In Excel for Microsoft 365 on Windows, select the source data, choose Insert > PivotTable, and enable Add this data to the Data Model in the creation dialog. Put the condition field in Filters or Rows, put the value field in Values, then open Value Field Settings and choose Distinct Count. Distinct Count is available for a PivotTable based on the Data Model.

A PivotTable is useful when you want to explore several categories, refresh results, or share a grouped summary. It is less convenient when a report needs a single number that updates directly from a criterion cell in a worksheet. For a simple dynamic count in supported Excel versions, the FILTER/UNIQUE formula is usually more direct. For very large datasets or repeatable reporting pipelines, Power Query may be a better fit, though it requires setting up and refreshing a query rather than entering one worksheet formula.

On Excel for the web, FILTER and UNIQUE are also available for Microsoft 365 users, but the interface for creating a PivotTable can differ from the Windows desktop steps. Mac versions of Microsoft 365 also support the functions; the formula itself is the same. Menu wording and available PivotTable options can vary by version, so check whether the Data Model and Distinct Count options are present before relying on that alternative.

Frequently Asked Questions

Can I count unique values while ignoring blanks?
Yes. Include the test for the counted range being nonblank, such as A2:A100<>"", in FILTER’s include argument. This excludes empty cells and formula results that are empty strings.

Does COUNTIF count unique values?
No. COUNTIF counts cells that meet a condition, including repeated values. To count distinct values with a condition in Microsoft 365, filter the values, pass them through UNIQUE, and count the result with ROWS.

How do I count distinct values for two conditions?
Multiply the two comparison arrays inside FILTER to require both conditions. Keep the nonblank test for the values being counted, then wrap the filtered results in UNIQUE and ROWS.

Why does my formula return zero?
Zero means no nonblank values passed the tests, or an error was converted to zero by IFERROR. Check the criterion cells, range alignment, and whether the source values contain unexpected spaces or mismatched data types.

Can I count unique values in an older version of Excel?
If the version lacks FILTER and UNIQUE, use a PivotTable with Add this data to the Data Model and the Distinct Count summary option, if available. Otherwise, an older array formula or helper-column approach may be needed and depends on the Excel version.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.