Dynamic Arrays
Count unique values with criteria (COUNTUNIQUEIFS pattern)
Count distinct customers or SKUs inside a filtered region or date window using FILTER, UNIQUE, and ROWS with a zero-match guard.
Why COUNTIFS counts orders instead of distinct customers
When Acme places two orders in the North region and Delta places one, COUNTIF(A2:A6, "North") reports 3 orders—not 2 unique accounts.
Filtering the Client column first with FILTER(B2:B6, A2:A6=C2, ""), deduplicating with UNIQUE, and counting rows with ROWS answers "how many distinct clients bought in North?"
| Region (A) | Client (B) | North Order? | Distinct North Client? |
|---|---|---|---|
| North | Acme | Yes (1) | Yes (1) |
| North | Acme | Yes (2) | Duplicate |
| South | Beta | No | No |
| North | Delta | Yes (3) | Yes (2) |
| South | Beta | No | No |
Zero-match safe distinct count formula
Count qualifying rows with a nonempty client name before FILTER. This handles missing regions and blank first matches without mistaking a later valid customer for no customers. The example uses text names.
=IF(SUMPRODUCT((A2:A6=C2)*(B2:B6<>""))=0,0,ROWS(UNIQUE(FILTER(B2:B6,(A2:A6=C2)*(B2:B6<>"")))))Compare raw order count vs distinct client count
In North, raw COUNTIF = 3 while unique clients = 2; in South, raw COUNTIF = 2 while unique clients = 1.
=COUNTIF(A2:A6, C2)Try the example
Use the original values or change them to test your understanding.