Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to count unique values matching criteria in Excel and Google Sheets
Heads up: Excel and Google Sheets do this differently.
Excel
=SUM(IF(FREQUENCY(IF(B2:B7="Criteria", MATCH(A2:A7, A2:A7, 0)), ROW(A2:A7)-ROW(A2)+1)>0, 1))Google Sheets
=ARRAYFORMULA(SUM(UNIQUE(IF(B2:B7="Criteria", A2:A7, ""))<>")Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Prepare your data with headers and ensure your criteria column is correctly labeled.
- 2In Excel, use the SUM and FREQUENCY functions combined with IF to count unique values meeting your criteria.
- 3In Google Sheets, use ARRAYFORMULA with UNIQUE and IF to achieve the same result.
- 4Replace 'A2:A7' and 'B2:B7' with your actual data range and criteria column.
- 5Adjust 'Criteria' to match the condition you want to apply.
Tips
- Ensure your data ranges are correctly specified to avoid errors.
- In Google Sheets, ARRAYFORMULA can handle arrays directly, simplifying complex formulas.
- Be cautious with large datasets as these formulas can be resource-intensive.
Need the Google Sheets version instead? Open Sheets variant in workspace →
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What if my criteria involve multiple conditions?
You can nest additional IF statements within the formula to handle multiple conditions.
How do I handle case sensitivity in my criteria?
Use EXACT instead of = for case-sensitive comparisons.
Can this method be used with dates or numbers?
Yes, the method works with any data type, including dates and numbers.