FormuladrillCOUNTIFS
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...) counts the rows where every condition is true. Each pair is a range and the condition to apply to it; all ranges must be the same size, and the logic is AND.
Syntax
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Part | What it is |
|---|---|
criteria_range1 | The first range to test. |
criteria1 | The condition for that range: "North", ">500", "<>", a cell reference. |
criteria_range2, criteria2 | More pairs, up to 127. Each range must have the same number of rows and columns as the first. |
Examples
=COUNTIFS(A2:A100, "North", B2:B100, "Shipped")Result: Number of Shipped orders from North
=COUNTIFS(C2:C100, ">=50", C2:C100, "<100")Result: Number of values from 50 up to but not including 100
Same range twice for a between condition.
=COUNTIFS(D:D, ">=" & DATE(2026, 1, 1), D:D, "<" & DATE(2026, 2, 1))Result: Number of January 2026 dates in column D
=COUNTIFS(A2:A100, E2, B2:B100, "<>Cancelled")Result: Rows for the region in E2 whose status is not Cancelled
Common mistakes
- #VALUE!: the ranges are different sizes (A2:A100 and B2:B200). Make every range identical in shape.
- A count of 0 when you expected OR logic: COUNTIFS(A:A, "North", A:A, "South") requires both at once. Add two COUNTIFS instead.
- Date criteria typed as text in quotes may be read in the wrong locale. Build them with DATE or reference a date cell.
- Criteria that are numbers stored as text in the data do not match a numeric criterion. Clean the column first.
Practice COUNTIFS
These drills check your answer on live data, free, no account needed.
- Count with two conditionsCount the orders from the region in E2 with an amount greater than the number in E3. Put the answer in E4.
Questions
COUNTIF or COUNTIFS?
COUNTIFS does everything COUNTIF does and also takes extra conditions, so it is safe to always use COUNTIFS.
How do I COUNTIFS with OR?
Add two COUNTIFS, or use =SUM(COUNTIFS(A:A, {"North","South"})) which counts each value in the braces and sums them.
Related
- COUNTIFHow to use COUNTIF in Excel to count cells equal to a value, above a number, containing text or not blank, with examples, a duplicate check and fixes.
- SUMIFSHow to use SUMIFS in Excel to add values that meet two or more conditions, with examples for region and status, date ranges, not-equal and wildcards.
- AVERAGEIFHow to use AVERAGEIF and AVERAGEIFS in Excel to average only the cells that match a condition, with examples for one region, positive values and two criteria.
- SUMPRODUCTHow the Excel SUMPRODUCT function multiplies ranges and sums the result, with examples for quantity times price, weighted average and sums with conditions.