Spreadsheet Practice

How to count unique values with a condition in Google Sheets

Google Sheets formula

=COUNTUNIQUE(FILTER(B2:B12,C2:C12=F1))

Filter first, then count the distinct values: =COUNTUNIQUE(FILTER(B2:B12,C2:C12=F1)).

Result on the example
3 in F2
Excel
See below

See it work

The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.

How it works

FILTER keeps the customers from the East orders, which include Northwind three times and Fabrikam twice. COUNTUNIQUE then counts each of them once: Northwind, Fabrikam and Adatum make 3. Change the region in F1 and the count follows.

=COUNTUNIQUE(FILTER(B2:B12,C2:C12=F1))

Result3

  1. B2:B1211 rows × 1 col: "Northwind", "Contoso", "Northwind", "Fabrikam", …

    A range: 11 cells in one column.

  2. C2:C1211 rows × 1 col: "East", "West", "East", "East", …

    A range: 11 cells in one column.

  3. F1"East"

    The value in F1.

  4. C2:C12=F1{11 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …

    The = operator tests whether the two sides are equal.

  5. FILTER(B2:B12,C2:C12=F1){6 rows × 1 col} "Northwind", "Northwind", "Fabrikam", "Adatum", …

    FILTER: Rows of a range that meet every condition. #N/A when none match.

    • rangeB2:B1211 rows × 1 col: "Northwind", "Contoso", "Northwind", "Fabrikam", …
    • condition1C2:C12=F1{11 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …
  6. COUNTUNIQUE(FILTER(B2:B12,C2:C12=F1))3

    COUNTUNIQUE: Counts distinct values, ignoring blanks.

    • value1FILTER(B2:B12,C2:C12=F1){6 rows × 1 col} "Northwind", "Northwind", "Fabrikam", "Adatum", …

In Excel

In Excel 2021 and later, count the rows of the unique filtered list: =ROWS(UNIQUE(FILTER(B2:B12,C2:C12=F1))).

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=COUNTUNIQUE(FILTER(B2:B12,(C2:C12=F1)+(C2:C12="North")))5Different resultAdd tests inside one condition for OR. Extra FILTER arguments, by contrast, all have to be TRUE. Customers in East or North: 5.
=ROWS(UNIQUE(FILTER(B2:B12,C2:C12=F1)))Excel only3The Excel 2021 version.

Common mistakes

  • When no row matches, FILTER returns #N/A and the count is wrong or an error. Check first: =IFNA(ROWS(UNIQUE(FILTER(B2:B12,C2:C12=F1))),0).
  • Google Sheets also has COUNTUNIQUEIFS, but FILTER handles OR logic and calculated conditions that the IFS form can't.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Count unique values with a condition

In F2, count how many different customers placed orders in the region in F1.

Questions

How do I count unique values for every region at once?

Use QUERY on the unique pairs: =QUERY(UNIQUE(B2:C12),"select Col2, count(Col1) group by Col2",0).

How do I count unique values in one month?

Filter by date: =COUNTUNIQUE(FILTER(B2:B12,MONTH(D2:D12)=6)), with dates in column D.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.