How to Use COUNTIFS in Excel
COUNTIFS counts rows that match multiple conditions. Here's the syntax, examples for text, dates, blanks, and thresholds, plus the mistakes that break the count.
COUNTIFS counts how many rows meet multiple conditions at the same time. Use it when you need answers like "how many open deals are over $5,000?" or "how many March expenses were in the Travel category?"
The syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)- criteria_range1 — the first range to test
- criteria1 — the first condition
- Add more
criteria_range/criteriapairs for additional filters
Every condition uses AND logic, so a row is counted only when all tests are true.
Basic example
If column A is Status and column B is Deal Value, count how many deals are marked Open and are worth more than 5000:
=COUNTIFS(A2:A100, "Open", B2:B100, ">5000")That counts only rows where both conditions are true.
Count rows between two dates
If column A contains dates and you want the number of entries in March 2026:
=COUNTIFS(A2:A100, ">="&DATE(2026,3,1), A2:A100, "<="&DATE(2026,3,31))This is the standard pattern for date windows in Excel.
Count text matches and non-blanks
If column A is Category and column B is Receipt Status, count how many Travel rows also have any value in the Receipt Status column:
=COUNTIFS(A2:A100, "Travel", B2:B100, "<>")"<>" means "not blank." For blank cells, use "".
Count text that contains a word
COUNTIFS supports wildcards too. If column A contains company names and column B contains Status, count active rows where the company name contains Corp:
=COUNTIFS(A2:A100, "*Corp*", B2:B100, "Active")| Criteria | What it does |
|---|---|
"Open" | Exact text match |
">5000" | Counts numbers over 5000 |
"" | Blank cells |
"<>" | Non-blank cells |
"*Corp*" | Text containing Corp |
Common mistakes
| Mistake | What happens | Fix |
|---|---|---|
| Criteria ranges are different sizes | #VALUE! | Make every criteria range the same shape |
| Forgetting quotes around operators | Wrong result | Use ">5000" or ">="&F2 |
| Using date text directly | Inconsistent counts | Use DATE() or cell references |
| Expecting OR logic | Too few rows counted | Add multiple COUNTIFS formulas together for OR logic |
COUNTIFS vs COUNTIF
COUNTIF handles one condition:
=COUNTIF(A2:A100, "Open")COUNTIFS handles two or more:
=COUNTIFS(A2:A100, "Open", B2:B100, ">5000")That makes COUNTIFS the better choice for trackers, pipelines, and expense logs where one column rarely tells the whole story.
The Griddy way
COUNTIFS is great for status dashboards and operational rollups, but building the criteria pairs manually gets tedious fast. Just say what you want:
"Count all reimbursable expenses from March with receipts still missing"
Griddy builds the COUNTIFS formula around your real columns and returns the count where you need it.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.
Use this on real templates
Count matching rows across real operating sheets
COUNTIFS is most useful when you need live counts by status, category, date window, or owner across finance and operations data.
Expense Tracker
Log every expense, track receipts, and generate category summaries. Free template for personal or business use.
CRM Lead Tracker
Track contacts, lead source, owner, next due date, and follow-up status in one lightweight CRM sheet. Keep hot opportunities and stale leads visible without paying for heavy sales software.
Small Business Budget
Plan revenue, direct costs, overhead, and EBITDA in one compact operating budget. Keep H1 totals, margin, and owner notes visible without building a giant finance model.