COUNTIFS Explained: Count Rows That Meet Every Condition

Every pair of range and condition must pass at the same position

The Excel syntax is COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]...). Microsoft allows up to 127 pairs and lists the function for Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, Excel 2016 and the Excel Web App.

Excel checks the ranges cell by cell. When the cell in range 1 and the cell at the same position in range 2 both pass, the count goes up by 1. Because of this, each extra range must have the same number of rows and columns as the first. The ranges do not have to be next to each other.

COUNTIF is the single-condition version: COUNTIF(range, criteria). Microsoft's COUNTIF page says that for several conditions you should use COUNTIFS.

A worked example: who met quota in Q1 and Q2

Microsoft's table lists 4 salespeople in rows 2 to 5, with Yes or No for quota in columns B, C and D (Q1, Q2, Q3). Davidoski: Yes, No, No. Burke: Yes, Yes, No. Sundaram: Yes, Yes, Yes. Levitan: No, Yes, Yes.

=COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes") returns 2, for Burke and Sundaram, the only people with Yes in both Q1 and Q2. =COUNTIFS(B2:D2,"=Yes") uses one row and returns 1, because Davidoski met quota only in Q1.

The ranges can also be rows laid out differently: =COUNTIFS(B5:D5,"=Yes",B3:D3,"=Yes") compares Levitan's row with Burke's row and returns 1, since only Q2 is Yes for both. That is the cell-by-cell rule at work.

Add a third pair to tighten the test. =COUNTIFS(B2:B5,"Yes",C2:C5,"Yes",D2:D5,"Yes") returns 1, because only Sundaram has Yes in all 3 quarters. Drop to the first pair and the answer was 2; with each added pair the count can stay the same or fall, never rise. That rule is a quick sanity check: if a longer COUNTIFS gives a bigger number than a shorter one, a range has slipped.

Count the other way too. =COUNTIFS(B2:B5,"No") returns 1 for Levitan's Q1, and a pair of "No" in B and "Yes" in C finds the same person, since Levitan failed Q1 but met Q2. Mixing Yes and No pairs is how you find people who improved from one quarter to the next.

Number and date conditions

Microsoft's second example uses numbers 1 to 6 in A2:A7 and the dates 5/1/2011 to 5/6/2011 in B2:B7. =COUNTIFS(A2:A7,"<6",A2:A7,">1") returns 4, the values 2, 3, 4 and 5. The same range can appear in 2 pairs to make a lower and an upper bound.

=COUNTIFS(A2:A7,"<5",B2:B7,"<5/3/2011") returns 2: the values 1 and 2 are below 5 and have dates before 5/3/2011. Instead of typing the limits, join the operator to a cell: =COUNTIFS(A2:A7,"<"&A6,B2:B7,"<"&B4) gives the same 2. Now you change a cell, not the formula.

Text conditions and wildcards

Put text in double quotes. Microsoft warns that without quotes you may get no value when you expect one. In Excel, ? matches one character, * matches any run of characters, and ~ before either one matches the literal symbol. COUNTIF ignores upper and lower case.

• "Yes" or "=Yes" counts cells equal to Yes.
• "A*" counts text that starts with A.
• "??" counts text of exactly 2 characters.
• "~*" counts cells that hold an actual asterisk.

Why the count is off: 4 traps

Blank criteria cells are the first trap. Microsoft says an empty cell used as a criteria argument is treated as 0, so a lookup cell you forgot to fill counts zeros, not blanks.

The second is hidden characters. Leading spaces, trailing spaces and nonprinting characters change what matches, and Microsoft names TRIM and CLEAN to remove them. The third is length: COUNTIF returns incorrect results for strings longer than 255 characters, and the fix is to join pieces with CONCATENATE or &. The fourth is a closed workbook: COUNTIF can return #VALUE! when it points at another workbook that is not open.

One limit surprises people. COUNTIF cannot count by background or font color, and Microsoft suggests a VBA user-defined function for that.

Google Sheets works the same way

Google documents COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...]) and says each extra range must have the same number of rows and columns as the first. Its examples are COUNTIFS(A1:A10, ">20", B1:B10, "<30") and COUNTIFS(C1:C100, "Yes"). The Google page does not state a pair limit or cover wildcards, so test those on a small range. Google lists COUNTIF, SUMIFS, AVERAGEIFS, IF and COUNT as related functions, which is a useful map if you need an average or a sum under the same conditions instead of a count.

Who it suits, and what to use for OR

COUNTIFS suits flat lists where each row is a record: orders by region and status, tickets by owner and month. It counts, it does not add. To add amounts under the same rules, use SUMIFS, which takes the same pairs after a sum range.

COUNTIFS only joins conditions with AND. For OR, count each condition on its own and add them, but take care not to double count a row that matches both. If your count looks wrong on the screen in front of you, Crade can read the sheet and help spot a hidden space.