Yes. To count distinct values that meet multiple criteria in Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(C2:C8,(A2:A8=E2)*(B2:B8=F2)*(C2:C8<>"")))),0). With categories in A2:A8, regions in B2:B8, names to count in C2:C8, and criteria in E2 and F2, this returns the number of different names matching both criteria, excluding blank names.
What “unique” means with COUNTIFS
COUNTIFS counts rows that meet one or more conditions; it does not, by itself, remove duplicate values from the count. For example, if a customer appears on three qualifying rows, COUNTIFS counts three rows. A distinct count treats those repeated customer names as one value. That is the distinction behind searches for “COUNTIFS unique” or “COUNTIFS distinct.”
Excel has no separate COUNTIFS option that switches the function to count unique results. Instead, combine criteria filtering with a distinct-value function, or use a helper column in versions without dynamic-array functions. First decide what counts as a distinct value: a customer name, order number, product, or another field. If two people can share a name, counting names will not necessarily count people accurately; a unique customer ID is usually a better field.
Count distinct matches in Microsoft 365
For a concrete example, suppose rows 2 through 8 contain category, region, and customer name in columns A, B, and C. The records include three Retail/East customers named Ana, Ben, and Ana again, plus Cara in Retail/East; there is also a blank customer name in a Retail/East row. Put Retail in E2 and East in F2. COUNTIFS would count five qualifying records, including the blank-name row, while the formula at the top returns 3: Ana, Ben, and Cara.
The formula uses FILTER to keep rows satisfying both criteria, UNIQUE to remove repeated names, and ROWS to count the remaining values. The multiplication between the two tests acts like AND: a row must match the category in E2 and the region in F2. The final test removes blank names. IFERROR returns 0 if no rows meet the conditions, rather than showing FILTER’s no-results error.
Enter the formula in an ordinary empty cell. It returns one number, so it does not need a special array-entry keystroke. FILTER, UNIQUE, and dynamic arrays are available in Microsoft 365 and Excel 2021 or later. In Excel for Mac, the formula works in supported versions too; Excel for the web also supports these functions. Older perpetual versions may not have them.
See which distinct values are being counted
When a total looks surprising, displaying the filtered unique list makes the result easier to check. With the same layout—categories in A2:A8, regions in B2:B8, names in C2:C8, and criteria in E2 and F2—enter this formula in an empty cell:
=UNIQUE(FILTER(C2:C8,(A2:A8=E2)*(B2:B8=F2)*(C2:C8<>"")))
Excel spills the matching names into the cells below, in the order they first appear in the source data. In the example, the list contains Ana, Ben, and Cara. The number of spilled names should match the count formula. Keep the cells below the formula clear, or Excel may show a #SPILL! error because it cannot place the results.
UNIQUE’s default behavior keeps one copy of each different value. Its optional exactly-once setting has a different purpose: it returns values that occur only once in the filtered array. That is not the usual meaning of “count unique.” In the example, an exactly-once list would omit Ana because she appears twice, even though Ana is one distinct customer.
Use more criteria or different conditions
To add a third criterion, multiply the existing tests by another comparison using equally sized ranges. For example, a status column could be required to equal a status criterion in another cell. All multiplied tests must be true for a row to remain in FILTER. If the intent is “match either of these two regions,” use an OR-style test rather than multiplying the two region comparisons; multiplying them would require one row to equal both regions at once.
Criteria cells can hold text, numbers, or dates, provided they are compared with compatible source values. For conditions such as “greater than a threshold,” build the comparison with an operator and a cell reference. COUNTIFS supports criteria operators and wildcards, but wildcards have meaning only for text matching: an asterisk can represent any sequence of characters, and a question mark can represent one character. Use exact criteria when partial matching is not wanted.
Excel’s normal text comparisons are not case-sensitive. Thus names that differ only by capitalization are generally treated as the same value by this approach. If capitalization must distinguish values, standard UNIQUE and COUNTIFS behavior will not meet that requirement; the data needs a case-sensitive method, such as a carefully designed helper calculation. Also consider whether leading or trailing spaces make two visually similar entries different. Cleaning imported text can prevent unexpected counts.
Workaround for older Excel versions
Excel versions without FILTER and UNIQUE can use a helper column to mark the first qualifying occurrence of each name. Assume the same data layout: category in A2:A8, region in B2:B8, customer in C2:C8, and criteria in E2 and F2. Put this formula in D2 and copy it down through D8:
=IF(AND(A2=$E$2,B2=$F$2,C2<>"",COUNTIFS($A$2:A2,$E$2,$B$2:B2,$F$2,$C$2:C2,C2)=1),1,0)
The expanding ranges in COUNTIFS examine the current row and the rows above it. A qualifying row receives 1 only if its customer name has not already appeared among qualifying rows; later duplicates receive 0. Blank names and rows that fail either criterion also receive 0. With the example data, the helper column marks Ana, Ben, and Cara once each.
With the helper values in D2:D8, enter this formula in a separate cell to total the marked first occurrences:
=SUM(D2:D8)
This method is compatible with older Excel editions that support COUNTIFS. Its trade-off is maintenance: the helper formula must be filled down as records are added, and the ranges may need extending. An Excel Table can make growing data easier to manage, though the formula references must be adapted to the table’s column names.
How this differs from a regular COUNTIFS
COUNTIFS remains useful when the question is “How many records match?” For example, with categories in A2:A8, regions in B2:B8, and the criteria in E2 and F2, use:
=COUNTIFS(A2:A8,E2,B2:B8,F2)
This returns the number of matching rows, including repeated names and rows where the customer cell is blank. It does not inspect column C for distinctness. Use this simpler formula for a record count; use the UNIQUE/FILTER approach when the question is how many different values in C meet the row criteria.
A PivotTable can also summarize distinct values, but the distinct-count option depends on adding the source data to the Data Model when creating the PivotTable. That can be convenient for a report with several grouped summaries, but it is more setup than a single formula. Power Query is another option for repeatable transformations: filter rows, remove duplicates using the relevant value and criteria columns, then load or summarize the result. These approaches can be preferable for larger recurring reports, while a formula is often easier for a small live worksheet.
Common problems and checks
- Unexpectedly high result: Check that the formula uses the field to be counted in the UNIQUE/FILTER range, not the category or region column. Verify the criteria cells and make sure each condition range covers the same rows.
- Unexpectedly low result: Look for extra spaces, inconsistent spellings, numbers stored as text, or criteria that do not exactly match the source. Two entries that look alike may contain different characters.
- Zero despite apparent matches: Confirm the selected criteria and source values use compatible types, especially for dates and numbers. Check that the formula references the intended columns and that the customer field is not blank.
- #NAME? error: The Excel version may not recognize FILTER or UNIQUE. Use the helper-column method or work in a version that supports those functions.
- #SPILL! error in the list formula: Clear cells in the output area and check for merged cells blocking the spilled results. The count formula returns a single value and normally does not need a spill area.
- Errors in the source data: An error value in a filtered field can propagate through the calculation. Find and resolve the source error or deliberately exclude error rows before relying on the total.
For a dependable result, compare the count formula with the displayed unique list on a small sample, then test a case with no matches and one with repeated values. This helps distinguish a genuine data issue from a formula-range mistake. If the source data changes often, use consistent identifiers and make sure the formula’s ranges expand with the data.
Frequently Asked Questions
Can COUNTIFS count unique values directly?
No. COUNTIFS counts matching rows, including duplicates. Combine FILTER and UNIQUE with a counting function in supported Excel versions, or use a helper column to flag first qualifying occurrences in older versions.
Does the formula count blank cells?
The formula shown excludes blank values in the field being counted by testing that the customer cell is not empty. If blanks should count as one distinct value, remove that blank-exclusion test, but be aware that the no-match case and blank results need separate handling.
Can I count distinct values using three criteria?
Yes. Add a third comparison to the FILTER include argument, multiplying it by the other tests for AND logic. Make sure its range is the same height as the other ranges and compare it with the correct criteria cell.
Is UNIQUE case-sensitive?
No. Standard Excel comparisons used here are not case-sensitive, so entries that differ only by uppercase and lowercase letters are generally treated as the same value. Use a case-sensitive approach if capitalization is meaningful in the data.
Why does COUNTIFS give a larger number than the distinct formula?
COUNTIFS counts every qualifying row. The distinct formula collapses repeated values in the selected field and, as written, excludes blank values. A larger row count is expected whenever qualifying names repeat.
Related
- Inspection Log Checklist in Excel: What to Include
- Checklist in Excel: Template Setup and Practical Tips
- Convert Percentage to Text in Excel: Methods and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Microsoft Excel Grade Formula for Scores and LettersIn Microsoft Excel, calculate a percentage grade by dividing points earned by points possible: with points earned in…
- Excel Spreadsheet Calendar FormulasThe core Excel formula for a dynamic calendar is =SEQUENCE(6,7,start_date-WEEKDAY(start_date)+1,1), which generates a 6-row by 7-column grid of…
- DATEDIF Not in Excel? How to Use the Hidden FunctionDATEDIF is available in Excel for Microsoft 365 on Windows, but it is a hidden compatibility function: it…
- Today’s Date in Excel: Shortcut and TODAY() FormulaIn Excel for Microsoft 365 on Windows, press Ctrl+; (Control plus semicolon) to insert today’s date as a…
- Add a Sequence in Excel with the SEQUENCE FunctionTo add a numbered sequence in Excel, enter =SEQUENCE(10) in a blank cell to spill the numbers 1…
- Split Function in C++: Options and ExamplesC++ does not have a general-purpose std::split function or a split() member on std::string. In C++20, you can…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.