SheetsdrillCOUNTIFS
COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...]) counts the rows where every criteria pair is met at the same time, an AND across columns. Each range must be the same size, and criteria use the same syntax as COUNTIF.
Syntax
=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])| Part | What it is |
|---|---|
criteria_range1 | The first column to test. |
criterion1 | Its condition: "Open", ">=10", "<>", E1, ">="&DATE(2026,1,1). |
criteria_range2, criterion2 | More pairs, all required. |
Examples
=COUNTIFS(B:B, "Open", C:C, "High")Result: Number of open tickets with high priority
=COUNTIFS(A:A, ">="&E1, A:A, "<="&E2)Result: Orders dated between the two dates in E1 and E2, inclusive
=COUNTIFS(B:B, "<>Cancelled", D:D, ">500")Result: Non-cancelled orders above 500
Common mistakes
- #VALUE! Array arguments to COUNTIFS are of different size: every criteria range must cover the same rows.
- Zero when a date column holds text dates; convert them with DATEVALUE or re-enter as dates.
- Forgetting the ampersand when a comparison uses a cell: ">="&E1, not ">=E1".
Practice COUNTIFS
These drills check your answer on live data, free, no account needed.
- Open high-priority ticketsCount the tickets that are both "High" priority and "Open". Answer in G1.
- Orders between two datesCount the orders dated from E1 up to and including E2. Point at the two date cells. Answer in E3.
Questions
How do I COUNTIFS a date range?
Use the same date column twice: =COUNTIFS(A:A, ">="&DATE(2026,1,1), A:A, "<="&DATE(2026,1,31)).
How do I count with OR?
Add COUNTIFS results, or use SUMPRODUCT with (B:B="High")+(B:B="Urgent").
Related
- COUNTIFHow COUNTIF in Google Sheets counts cells equal to a value, above a number, containing text or not blank, with examples, wildcards and the errors that give 0.
- SUMIFSHow SUMIFS in Google Sheets adds values that meet several conditions at once, with examples for a rep and month, date ranges, not-equal and wildcards.
- AND and ORHow AND and OR work in Google Sheets to test several conditions at once, how to use them in IF, and why they fail inside FILTER and ARRAYFORMULA, with fixes.
- FILTERHow the FILTER function in Google Sheets returns only rows that match one or more conditions, with examples for AND, OR, dates and text, plus common errors.