The formula
Excel 365Excel
=IFERROR(ROWS(UNIQUE(FILTER(B2:B9,A2:A9=B11))),0)In E2 → 3
Any Excel & Google SheetsExcelGoogle Sheets
=SUMPRODUCT((A2:A9=B11)/COUNTIFS(A2:A9,A2:A9,B2:B9,B2:B9))In E3 → 3
Google SheetsGoogle Sheets
=COUNTUNIQUE(FILTER(B2:B9,A2:A9=B11))In E4 → 3
✓ Tested. All 2 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown. 1 result for formulas tagged Google Sheets was checked with a reference implementation of the documented behavior, because LibreOffice can't run that function.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Customer | |||
| 2 | East | Acme | 3 | ||
| 3 | East | Birch | 3 | ||
| 4 | West | Acme | 3 | ||
| 5 | East | Acme | |||
| 6 | North | Cobalt | |||
| 7 | East | Dune | |||
| 8 | West | Birch | |||
| 9 | East | Birch | |||
| 10 | |||||
| 11 | Region | East |
How it works
- FILTER(B2:B9,A2:A9=B11)
- Customers in the chosen region.
- UNIQUE(…)
- Each customer once.
- ROWS(…)
- How many. IFERROR returns 0 when nothing matches.
- …/COUNTIFS(A2:A9,A2:A9,B2:B9,B2:B9)
- Each region-customer pair counts as 1/(times it appears), so duplicates add up to exactly 1.
Common errors and fixes
Returns 1 when there are no matches?
COUNTA(UNIQUE(FILTER(…))) counts the error. Use ROWS with IFERROR as shown.
Why does the formula return #DIV/0! in SUMPRODUCT?
The ranges contain blank rows. Limit the range or add &"" to each COUNTIFS criteria.
Does this work in Google Sheets?
Yes. Use the formulas tagged "Google Sheets" on this page; formulas tagged only "Excel" use Excel-specific syntax.