Countifs Unique Values in Google Sheets: The Right Formula

To count distinct values that meet several conditions in Google Sheets, combine COUNTUNIQUE with FILTER; COUNTIFS by itself counts matching rows, including duplicates. For example, with customer names in A2:A, regions in B2:B, and payment status in C2:C, use =IFNA(COUNTUNIQUE(FILTER(A2:A, B2:B="East", C2:C="Paid", A2:A<>"")), 0) to count nonblank customers with East-region, Paid records.

What “COUNTIFS unique values” means

The phrase usually describes a practical goal rather than a single Google Sheets function: count the different entries in one column after applying conditions in one or more other columns. A sales table might contain many transactions for the same customer. If you want to know how many different customers bought in a region, counting qualifying rows will overstate the result whenever a customer appears more than once.

COUNTIFS is designed to count rows or cells that satisfy multiple criteria. It does not automatically remove duplicates. COUNTUNIQUE counts distinct values, while FILTER narrows the data to rows that satisfy your criteria. Used together, they answer the distinct-count question.

It helps to define what should be unique before writing a formula. You might mean unique customer names, order IDs, email addresses, or combinations such as customer-and-product pairs. The formula must count the field—or combination of fields—that represents the thing you want to measure.

The basic formula for distinct matches

Suppose each row is a transaction, customer names are in A2:A, regions are in B2:B, and payment statuses are in C2:C. To count customers with a matching region and status stored in E2 and F2, enter:

With customer names in A2:A, regions in B2:B, statuses in C2:C, the selected region in E2, and status in F2:

=IFNA(COUNTUNIQUE(FILTER(A2:A, B2:B=E2, C2:C=F2, A2:A<>"")), 0)

FILTER returns names only from rows where both conditions are true and the name is not blank. COUNTUNIQUE counts each distinct name once. If no rows meet the conditions, FILTER normally returns a no-matches error; IFNA turns that outcome into 0, which is easier to use in a report or chart.

The criteria in this example use exact matches. For instance, if E2 contains East and F2 contains Paid, the formula looks for rows matching both values. A row matching only the region or only the status is not included. The criteria ranges and the values being filtered should cover corresponding rows; mismatched range sizes can cause an error.

Using fixed criteria or cell references

You can type criteria directly into a formula, which is handy for a one-off count, or refer to cells so the result updates when you change a selection. Cell references are often more useful in a reusable dashboard: put a region in E2 and a status in F2, then use the formula above.

With customer names in A2:A, regions in B2:B, and statuses in C2:C, this version counts distinct East-region customers marked Paid:

=IFNA(COUNTUNIQUE(FILTER(A2:A, B2:B="East", C2:C="Paid", A2:A<>"")), 0)

Text criteria need quotation marks. A cell reference such as E2 does not go inside quotation marks, because the formula needs to read the cell’s current value. For a date condition, compare the date column with a cell containing a real date value rather than text that merely looks like a date. Dates with different display formats can still match if their underlying date values are equal.

Counting distinct combinations of columns

Sometimes a single column is not enough to identify a unique item. For example, the same product may be sold by different vendors, and each vendor-product pair should count separately. In Google Sheets, filter the relevant columns together, then use UNIQUE to keep distinct rows and ROWS to count them.

With vendor names in A2:A, regions in B2:B, product names in C2:C, and the chosen region in E2, this counts distinct vendor-product pairs in that region. It excludes rows where either part of the pair is blank:

=IFNA(ROWS(UNIQUE(FILTER({A2:A,C2:C}, B2:B=E2, A2:A<>"", C2:C<>""))), 0)

The array expression {A2:A,C2:C} makes a two-column result. UNIQUE compares complete rows, so the same vendor with two different products counts twice, while repeated rows for the same vendor and product count once. If blank vendors or products are meaningful records in your data, remove the relevant nonblank condition rather than excluding them automatically.

Distinct values versus values that appear once

“Unique” can mean two different things. A distinct count includes a value once even if it appears many times. A value that appears exactly once is a singleton and is a narrower category. For example, customer names Alex, Alex, and Sam represent two distinct names, but only Sam appears exactly once.

To identify singleton customers among rows meeting criteria, first calculate how many qualifying rows each name has. With names in A2:A, regions in B2:B, statuses in C2:C, and selected criteria in E2 and F2, enter this in D2 and fill it down alongside the data:

=IF(A2="", 0, COUNTIFS($A$2:$A, A2, $B$2:$B, $E$2, $C$2:$C, $F$2))

This helper formula counts rows for that name that also match the selected region and status. To count the different names whose qualifying count is 1, with the helper results in D2:D and the same fields and criteria cells, use:

=IFNA(COUNTUNIQUE(FILTER(A2:A, D2:D=1, B2:B=E2, C2:C=F2, A2:A<>"")), 0)

Do not use the singleton method when you simply want a customer count that ignores duplicate transactions. In that common case, use COUNTUNIQUE(FILTER(...)) directly. A singleton formula answers whether each value has exactly one qualifying record, not how many distinct values appear.

Common problems and how to fix them

Counting rows instead of people or items: A plain COUNTIFS returns the number of matching records. Replace it with a filtered distinct count if duplicates should count only once.
Unexpected blank entries: Add a condition such as A2:A<>"" to exclude blank values from the field being counted. Check that cells that look empty do not contain spaces or formulas returning text.
No-match errors: If the filter might find nothing, wrap the result in IFNA(..., 0) to return zero instead of an error.
Criteria that do not match: Look for extra spaces, inconsistent spelling, or different data types. A number stored as text may not behave like a numeric value, and a date stored as text may not match a real date.
Open-ended ranges: Ranges such as A2:A are convenient as the sheet grows, but can make formulas work over many empty rows. On a very large sheet, bounded ranges such as A2:A5000 can be more efficient if you keep the limits current.
Duplicate-looking names: “Jordan Lee” and “Jordan Lee ” may be treated as different text because the second value has a trailing space. Clean source data when appropriate, for example with TRIM, or use a helper column containing normalized names.

Google Sheets formulas use commas between arguments in the examples here. Spreadsheet locale settings can affect separators and how dates are entered, so if Sheets reports a formula parse error, check the locale under File > Settings and use the separator expected by that locale.

Alternatives and the Excel difference

For a visible list of matching names rather than just a count, use UNIQUE around the filtered results. With names in A2:A, regions in B2:B, and statuses in C2:C, this returns distinct nonblank names for East-region Paid rows:

=UNIQUE(FILTER(A2:A, B2:B="East", C2:C="Paid", A2:A<>""))

That list can help you inspect which records are being counted and verify the result. Another option is a pivot table when you want to explore counts by several categories interactively, though a pivot table’s ordinary row count is not automatically a distinct count. For a formula-driven result that updates with criteria cells, the filtered-count approach is usually direct and easy to audit.

Google Sheets has a COUNTUNIQUE function. Excel does not use that function name; in current Microsoft 365 Excel, a corresponding distinct count can be built with ROWS(UNIQUE(FILTER(...))). The formulas in this article are written for Google Sheets in a web browser, as requested. Function availability and details can differ between spreadsheet programs, so do not paste a Sheets-only COUNTUNIQUE formula into Excel unchanged.

Frequently Asked Questions

Can COUNTIFS count unique values in Google Sheets?
COUNTIFS counts cells or rows matching conditions; it does not deduplicate them. Combine COUNTUNIQUE and FILTER when the goal is to count distinct values among matching rows.

How do I count unique names that meet two conditions?
Filter the name column using both condition ranges, then count the filtered distinct names. For example, the first formula in this article counts unique customers matching a region and a payment status.

How do I return zero when there are no matches?
Wrap the distinct-count expression in IFNA(..., 0). This handles the no-results error from FILTER and displays zero.

Does COUNTUNIQUE ignore blank cells?
For a predictable result, explicitly exclude blanks in the filtered value column with a condition such as A2:A<>"". This also makes the formula’s intent clear to anyone maintaining the sheet.

Can I count unique combinations instead of unique values in one column?
Yes. Filter the two or more columns together, apply UNIQUE to the resulting rows, and count those rows with ROWS. This treats each distinct combination as one item.

Why does my formula count the same person twice?
The entries may not actually be identical: leading or trailing spaces, spelling differences, or inconsistent identifiers can create separate values. Inspect the source data and normalize it if those differences should be treated as the same person.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.