How to count unique values with criteria in Excel

One condition: ROWS, UNIQUE and FILTER

The sheet is a coffee wholesaler's order log. The question is "how many different customers ordered in the East region?" COUNTIFS cannot answer it: it counts rows, and Harbor Cafe ordered three times.

Read the formula from the inside out. FILTER keeps the customer names on East rows. UNIQUE removes the repeats. ROWS counts how many names are left. COUNTA works in place of ROWS when there are matches, but it behaves badly when there are none (see below). FILTER and UNIQUE need Excel 365, Excel 2021 or later, or Excel for the web.

Trailing spaces split one customer into two

B6 is "Harbor Cafe " with a space at the end. UNIQUE treats it as a different value, so Harbor Cafe is counted twice. It ignores upper and lower case, but not spaces.

Put TRIM around the FILTER result. Every name is trimmed before UNIQUE compares them, so the two spellings become one. If several people type the names, a drop-down list (Data > Data Validation > List) stops the problem at the source.

Several conditions: multiply for AND, add for OR

Each condition inside FILTER gives a TRUE or FALSE for every row. Multiplying two conditions keeps only rows where both are TRUE, so (C2:C10="East")*(E2:E10>=100) means East orders of 100 or more. Harbor Cafe's grinder and Pine Bakery's 126 order qualify, so the count is 2.

Adding conditions means OR: (D2:D10="Beans 1kg")+(D2:D10="Grinder") keeps rows with either product, which counts 3 different customers here. Put each condition in its own brackets, or Excel applies the operators in the wrong order.

For a date range, compare against cells that hold real dates. This formula counts the different customers who ordered in October, using the dates in J1 and J2, and also drops rows with no customer name.

Blank cells get counted as a value

Row 9 is a West order with no customer name. FILTER returns an empty cell as 0, and UNIQUE keeps that 0 as one more value, so the count includes a customer who does not exist. =UNIQUE(FILTER(B2:B10,C2:C10="West")) lists Lumen Hotel, Oak Diner and 0.

Add (B2:B10<>"") as an extra condition whenever the counted column can have gaps. It costs nothing when there are no blanks, so it is a good habit.

When nothing matches: COUNTA says 1, ROWS gives #CALC!

North has no orders. FILTER with no matching rows returns #CALC!, and ROWS passes that error on. The tempting fix is FILTER's third argument, if_empty, but that backfires: FILTER then returns one empty string, UNIQUE keeps it, and COUNTA counts it as one value.

Use IFERROR around the whole count instead: =IFERROR(ROWS(UNIQUE(FILTER(B2:B10,C2:C10="North"))),0). Any #CALC! from an empty result becomes 0. This is why ROWS is the better counter: when FILTER finds nothing it fails loudly instead of returning a believable 1.

A summary table with one formula per group

List the groups in a column, here the regions in G2:G4, and point the condition at that cell instead of typing "East". Lock the data ranges with $ so they stay put when you fill the formula down. This version combines everything above: TRIM for spaces, a blank check and IFERROR for groups with no orders.

To count something other than customers, change the first range. =ROWS(UNIQUE(FILTER(D2:D10,TRIM(B2:B10)="Harbor Cafe"))) counts the different products Harbor Cafe bought, which is 2: Beans 1kg and Grinder.

Excel 2019 and older: SUMPRODUCT, or a PivotTable

Without UNIQUE and FILTER, the classic formula is =SUMPRODUCT((C2:C10="East")*(B2:B10<>"")/COUNTIFS(B2:B10,B2:B10&"",C2:C10,C2:C10&"")). For each row, COUNTIFS counts how many rows share its customer and region. A customer with three East orders contributes 1/3 three times, which adds up to 1. The &"" stops blank cells from producing a division by zero, and the (B2:B10<>"") part stops blanks being counted.

It is hard to read and gets slow on large ranges, because COUNTIFS runs once per row. It also counts "Harbor Cafe " separately, so clean the data first. For a report, a PivotTable is often the better tool: it counts distinct values per group without any formula.

1. Click inside the data and choose Insert > PivotTable.
2. Tick "Add this data to the Data Model" and click OK. Without it, Distinct Count is not offered.
3. Drag Region to Rows and Customer to Values.
4. Click the Customer field in Values, choose Value Field Settings, select Distinct Count and click OK.
5. To add a condition such as a date range, drag Date to Filters, or use a slicer.