COUNTIFS with multiple criteria in Excel

The syntax

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …). You can add up to 127 range/criterion pairs. A row is counted only if it meets all of them, so COUNTIFS always combines its conditions with AND. All ranges must have the same number of rows and columns; if one range is longer, COUNTIFS returns #VALUE!.

Text criteria are not case-sensitive: "open", "Open" and "OPEN" count the same rows. Comparison operators go inside the quotes together with the value: ">=2", "<>Closed". Without an operator COUNTIFS tests for equality.

Step by step: two criteria, then a range

For "open tickets with high priority", COUNTIFS checks the first pair (E2:E11 = "Open") row by row, then the second pair (D2:D11 = "High"). Rows 4, 6, 8 and 10 are open, but only rows 4 and 6 are also High, so the result is 2.

A range on one column needs two criteria on the same range. "First reply between 2 and 10 hours" is =COUNTIFS(F2:F11,">=2",F2:F11,"<=10"): the values 5, 2, 3, 4 and 9 qualify, so the count is 5. The same pattern counts a date range: =COUNTIFS(A2:A11,">="&DATE(2026,3,1),A2:A11,"<"&DATE(2026,4,1)) returns 7 tickets for March. Using "less than the first day of the next month" instead of "less than or equal to 31 March" keeps tickets with a time stamp on the last day.

Criteria from cells and calculations

A criterion can be a plain cell reference, =COUNTIFS(D2:D11,F21,E2:E11,"Open"), which makes the count interactive: type Medium in F21 and the result is 1, type High and it becomes 2. When the criterion needs an operator, join the operator in quotes and the cell or calculation with &: ">"&AVERAGE(F2:F11) counts tickets slower than the average first reply of 10 hours, which gives 3. Writing ">AVERAGE(F2:F11)" inside the quotes does not work, because COUNTIFS then compares with that literal text.

Worked example: counting support tickets with COUNTIFS

Ten support tickets (yellow). Each count below answers one question; F21 is a criteria cell you can change.

Worked example: counting support tickets with COUNTIFS

	A	B	C	D	E	F
1	Date	Channel	Topic	Priority	Status	First reply (h)
2	24 Feb 2026	Email	Refund request	High	Closed	5
3	27 Feb 2026	Phone	Login issue	Medium	Closed	1
4	02 Mar 2026	Chat	Refund status	High	Open	2
5	05 Mar 2026	Email	Invoice copy	Low	Closed	26
6	09 Mar 2026	Email	Refund request	High	Open	31
7	12 Mar 2026	Phone	Delivery delay	High	Closed	3
8	18 Mar 2026	Chat	Login issue	Medium	Open	1
9	23 Mar 2026	Email	Delivery delay	Medium	Closed	18
10	30 Mar 2026	Phone	Refund request	Low	Open	4
11	02 Apr 2026	Email	Address change	Medium	Closed	9
12
13	Question					Count
14	Open tickets with High priority					2
15	Email tickets answered within 24 hours					3
16	Tickets created in March 2026					7
17	Tickets about refunds (wildcard)					4
18	Phone or Chat (OR with an array constant)					5
19	First reply between 2 and 10 hours					5
20	Not closed (not equal to)					4
21	Priority to count (criteria cell)					Medium
22	Open tickets with the priority in F21					1
23	Slower than the average first reply					3
24	Share of March tickets still open					57.1%

Fictitious tickets. Dates are real Excel dates; COUNTIFS compares their serial numbers, so the date format does not matter.

Wildcards, blanks and "not equal"

An asterisk stands for any number of characters, a question mark for exactly one. "*refund*" counts every topic that contains "refund" anywhere, here 4. "Refund*" would only count topics that start with it. To count a real asterisk or question mark, put a tilde in front: "~*".

"<>Closed" counts everything that is not Closed, which includes blank cells in the range. "<>" on its own counts non-blank cells and "" counts blank cells. Combine them, for example =COUNTIFS(E2:E11,"<>Closed",E2:E11,"<>"), when empty rows at the end of a list should not be counted as open.

OR logic: one condition or another

Because COUNTIFS only knows AND, an OR question needs one count per value. =COUNTIFS(B2:B11,"Phone")+COUNTIFS(B2:B11,"Chat") works, and so does the shorter array constant =SUM(COUNTIFS(B2:B11,{"Phone","Chat"})): COUNTIFS returns {3,2}, SUM adds them to 5. This only works when the values are mutually exclusive in one column. For OR across two different columns, for example "Phone or High priority", adding two counts would count the rows that are both twice; subtract the overlap or use SUMPRODUCT.

Two lists of alternatives in two different columns can be combined in one formula if one array constant is written with commas (a row) and the other with semicolons (a column): =SUM(COUNTIFS(B2:B11,{"Phone","Chat"},D2:D11,{"High";"Medium"})) counts all four combinations of channel and priority, which gives 4 in the example: one ticket each for Phone/High, Phone/Medium, Chat/High and Chat/Medium. With commas in both constants Excel would pair the values position by position and only count Phone/High and Chat/Medium.

COUNTIF, COUNTIFS, SUMIFS or a PivotTable?

COUNTIF takes exactly one condition and COUNTIFS any number, so COUNTIFS with one pair can replace COUNTIF everywhere. When you need a total instead of a count, SUMIFS uses the same criteria rules with a sum range in front, and dividing a COUNTIFS by another count gives a share, like the 57.1% of March tickets that are still open. A PivotTable is faster for exploring, while COUNTIFS keeps the logic visible in cells and updates instantly when the data changes.

Common mistakes

• Ranges of different size. COUNTIFS(E2:E11,"Open",D2:D12,"High") returns #VALUE!. Every criteria range needs the same shape.
• Operator outside the quotes. COUNTIFS(F2:F11,>=2) is not a valid formula; write ">=2", or ">="&H1 when the value is in a cell.
• Cell reference inside the quotes. ">H1" compares with the text H1. Join it: ">"&H1.
• Expecting OR from extra pairs. Adding a second pair on the same column, such as Phone and Chat, always returns 0, because no cell is both. Use SUM with an array constant.
• Dates typed as text. A date criterion like ">=01.03.2026" depends on regional settings. Build it with DATE(2026,3,1).
• Long ID numbers. COUNTIFS treats numeric-looking text as numbers, so IDs with more than 15 digits that differ only at the end can be counted as equal. Use SUMPRODUCT with an exact comparison for such keys.