Conditional formula page
COUNTIFS Formula for Multiple Criteria
Use COUNTIFS when a row should count only if several criteria are true. This example counts completed tasks owned by Maya and shows reusable date and dashboard patterns.
Counting rows by multiple text, number, or date criteria with COUNTIFS.
Returns 2 because Maya owns two Complete tasks in the sample data.
Copy formulas
=COUNTIFS(C2:C100, "Complete", B2:B100, "Maya")=COUNTIFS(C2:C100, "Complete", B2:B100, "Maya")Excel and Google Sheets use the same COUNTIFS criteria-pair syntax. COUNTIFS counts rows and does not require a separate count range.
Example data
| Task | Owner | Status | Due Date | Hours |
|---|---|---|---|---|
| Import leads | Maya | Complete | 2026-01-06 | 3 |
| Clean headers | Nico | In Progress | 2026-01-08 | 2 |
| Review budget | Maya | Complete | 2026-01-12 | 4 |
| Publish report | Iris | Blocked | 2026-01-15 | 1 |
Returns 2 because Maya owns two Complete tasks in the sample data.
How the formula works
- C2:C100 is the first criteria range and must equal Complete.
- B2:B100 is the second criteria range and must equal Maya.
- COUNTIFS applies AND logic, so both criteria must be true on the same row.
- COUNTIFS returns a row count, not a sum of hours or amounts.
| Syntax piece | Role in the formula |
|---|---|
| C2:C100, Complete | Keeps task rows whose Status value is Complete. |
| B2:B100, Maya | Keeps Complete rows whose Owner value is Maya. |
| COUNTIFS | Counts rows where both criteria are true; it does not add the Hours column. |
Verified examples
=COUNTIFS(C2:C100, "Complete", B2:B100, "Maya")Excel: The sample task table contains two rows where Owner is Maya and Status is Complete. Returns: 2
=COUNTIFS(B2:B100, "Maya", D2:D100, "<"&DATE(2026,1,10))Google Sheets: Only Import leads is owned by Maya and due before 2026-01-10. Returns: 1
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| COUNTIFS returns 0 for a name that appears in the table | The owner or status text contains extra spaces or differs from the typed criterion. | Compare one source cell to the criterion, then clean imported text before widening the formula ranges. |
| COUNTIFS returns #VALUE! | The criteria ranges do not cover the same number of rows. | Align all pairs, such as C2:C100 with B2:B100 and any additional date range. |
| A date condition misses expected tasks | Due dates are text values or the upper date boundary excludes the final day unintentionally. | Convert the column to real dates and use an explicit next-day or next-period upper boundary. |
When not to use this formula
- Use SUMIFS when Hours or another numeric field should be added, and use FILTER when the matching task rows must be displayed.
Alternatives
| Alternative | When to use it |
|---|---|
| COUNTIFS Formula Builder | Use to add or remove criteria pairs without hand-editing the formula syntax. |
| SUMIFS Formula | Use when the result should total a numeric column instead of counting rows. |
Related formulas
Official references
- COUNTIFS function from Microsoft
FAQ
Can COUNTIFS count three or more conditions?
Yes. Add another criteria range and criterion pair for each independent condition, keeping every range aligned to the same rows.
Why does COUNTIFS not use the Hours column?
COUNTIFS counts qualifying rows. Use SUMIFS with Hours as its sum range when you need a total number of hours.