Maths and conditional totals

Excel COUNTIFS Function: Count Rows Meeting Several Conditions

Q: Can COUNTIFS use OR logic?

Not within one call — every criteria pair narrows with AND. Add two COUNTIFS together for OR, and subtract a third counting the overlap if a row could satisfy both.

Q: Why does my date criterion not work?

Dates need the operator joined to the value with &, and the value needs to be a real date rather than text: ">="&DATE(2024,1,1) or ">="&F1 where F1 holds a date. Typing ">=01/01/2024" inside quotes compares against a string.

Counts rows where every one of several conditions is true at once.

COUNTIFS is COUNTIF with room for more than one condition. How many orders were in the North region and above 20 units; how many tickets were opened this month and are still unresolved. Each condition narrows the count further, and every one must hold for a row to be counted.

Unlike the SUMIF/SUMIFS pair, there is no argument-order trap here: COUNTIFS takes range-and-criteria pairs from the start, exactly as COUNTIF does, because there is no separate range to count — it counts the rows themselves.

It is also the natural way to count between two values. Give it the same range twice with opposite comparisons, and you have a band: at least this, at most that. The same trick with a date column gives you a date range.

Syntax

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)

Arguments

criteria_range1 Required: The first range to test. All ranges must be the same size.
criteria1 Required: What it must match: a value, a quoted comparison (">20"), a cell reference, or a wildcard pattern.
criteria_range2, criteria2, … Optional: Further pairs, up to 127. Every pair must hold for the row to count.

The example data

Headers in row 1, data in A2:D6. Note the blank in C4 and the text in C6.

	A	B	C	D
1	Item	Units	Unit price	Region
2	Cordless drill	12	89.99	North
3	Extension lead	40	12.5	South
4	Safety goggles	8		North
5	Work gloves	25	5.4	South
6	Tool belt	6	n/a	North

Worked examples

=COUNTIFS(D2:D6, "North")

Result: 3

One condition, written as COUNTIFS. Identical to the COUNTIF equivalent.

=COUNTIFS(D2:D6, "North", B2:B6, ">10")

Result: 1

Two conditions. Only the drill row is both North and above ten units.

=COUNTIFS(B2:B6, ">=10", B2:B6, "<=30")

Result: 2

The same range twice for a band: between 10 and 30 inclusive, which catches 12 and 25.

=COUNTIFS(D2:D6, "South", B2:B6, ">100")

Result: 0

A legitimate zero — nothing satisfies both. Worth distinguishing from the accidental zeros below.

Now practise it

Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.

Open the exercise: COUNTIF Function

Common errors and how to fix them

Returns 0 when rows should match

Why it happens: One criterion does not match the stored data: trailing spaces, numbers stored as text, or a comparison operator written without quotes.

How to fix it: Remove conditions one at a time. The one that brings the count back from 0 is the broken one.

#VALUE!

Why it happens: The criteria ranges are different sizes.

How to fix it: Make every range span the same rows. Whole-column references guarantee it.

Comparison against a cell does nothing

Why it happens: Writing ">F1" searches for the literal text ">F1" rather than comparing to F1.

How to fix it: Join the operator to the reference: ">"&F1.

Counts unexpected rows

Why it happens: * and ? act as wildcards, so a criterion containing them matches more than intended.

How to fix it: Escape them with a tilde: "~*" matches a literal asterisk.

Tips worth knowing

Count between two dates with the same column twice: =COUNTIFS(E:E, ">="&F1, E:E, "<="&F2).
Count non-blank rows meeting a condition by adding a "<>" criterion on the column that must be filled.
COUNTIFS conditions are always ANDed. For OR, add two COUNTIFS and subtract the overlap.
Build the criteria in cells rather than the formula, and the sheet becomes a small interactive report.

Frequently asked questions

How do I count between two numbers?

Use the same range twice with opposite operators: =COUNTIFS(B2:B100, ">=10", B2:B100, "<=30"). Both conditions apply to every row, so only values inside the band are counted.

Can COUNTIFS use OR logic?

Not within one call — every criteria pair narrows with AND. Add two COUNTIFS together for OR, and subtract a third counting the overlap if a row could satisfy both.

Why does my date criterion not work?

Dates need the operator joined to the value with &, and the value needs to be a real date rather than text: ">="&DATE(2024,1,1) or ">="&F1 where F1 holds a date. Typing ">=01/01/2024" inside quotes compares against a string.

Related functions

Guides that use it

Longer reads where this function does real work in a real sheet.

All functions