Cellwise Try it free

Excel guide · 3 min read

COUNTIF vs COUNTIFS: what is the difference?

Quick answerCOUNTIF counts cells that meet one condition: =COUNTIF(range, criteria). COUNTIFS counts rows that meet every condition you give it: =COUNTIFS(range1, criteria1, range2, criteria2, ...). With a single condition they return the same number, so you can use COUNTIFS for everything; COUNTIF is just shorter to type.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDE
1TicketAgentPriorityStatusHours
2T-1041MayaHighOpen3.5
3T-1042LeoLowClosed1
4T-1043MayaMediumClosed2
5T-1044PriyaHighOpen6
6T-1045LeoHighClosed4.5
7T-1046MayaHighClosed2.5
8T-1047PriyaLowOpen0.5
9T-1048LeoMediumOpen3

One condition: both give the same answer

This help desk log has eight tickets. To count the open ones, COUNTIF takes the range to check and the condition. COUNTIFS with one pair, =COUNTIFS(D2:D9,"Open"), returns exactly the same 4.

Pick COUNTIF when you know you will only ever have one condition and want the shortest formula. Pick COUNTIFS if there is any chance someone will add a second condition later, because you can just append another pair instead of rewriting the formula.

=COUNTIF(D2:D9,"Open")

Result: 4

4 tickets are open (T-1041, T-1044, T-1047, T-1048).

Two or more conditions: only COUNTIFS

COUNTIF has room for one range and one condition, full stop. As soon as you need "Maya's high-priority tickets", you need COUNTIFS. Each extra condition is another range, criteria pair, and a row is counted only when all of them are true (AND logic).

Every range must be the same size and shape. B2:B9 with C2:C8 returns #VALUE!, which is the most common COUNTIFS error.

=COUNTIFS(B2:B9,"Maya",C2:C9,"High")

Result: 2

2: T-1041 and T-1046 are both Maya and High. T-1043 is Maya but Medium, so it is not counted.

A range of numbers: two conditions on the same column

"Between" is two conditions, so it is COUNTIFS territory even though only one column is involved. List the same range twice, once with the lower bound and once with the upper bound.

Comparison operators go inside quotes. To compare with a cell, join them with &: ">="&G1.

=COUNTIFS(E2:E9,">=2",E2:E9,"<=4")

Result: 4

4 tickets took between 2 and 4 hours (3.5, 2, 2.5 and 3).

OR logic: add COUNTIFs together

Neither function does OR between conditions on one column. =COUNTIFS(C2:C9,"High",C2:C9,"Medium") asks for tickets that are High and Medium at the same time, so it returns 0. To count High or Medium, add two counts.

This works because no ticket can be both, so nothing is counted twice. If your OR spans two different columns (High priority OR assigned to Leo), adding counts double-counts rows that match both; subtract the overlap with a COUNTIFS of both conditions.

=COUNTIF(C2:C9,"High")+COUNTIF(C2:C9,"Medium")

Result: 6

6: four High plus two Medium tickets.

OR across columns without double counting

High priority or assigned to Leo: 4 High tickets plus 3 Leo tickets, minus the 1 ticket that is both (T-1045). The last term removes the overlap.

In Excel 365 you could also write =ROWS(FILTER(A2:A9,(C2:C9="High")+(B2:B9="Leo"))), but the add-and-subtract version works in every version of Excel.

=COUNTIF(C2:C9,"High")+COUNTIF(B2:B9,"Leo")-COUNTIFS(C2:C9,"High",B2:B9,"Leo")

Result: 6

6 tickets are High priority, Leo's, or both.

Common mistakes

Ranges of different sizes in COUNTIFS

Every criteria range must have the same number of rows and columns. Check that each one starts and ends on the same rows, for example all 2:9.

Expecting COUNTIFS to do OR

Multiple pairs are always AND. For OR, add separate COUNTIF results, and subtract the overlap when the conditions are on different columns.

Writing the operator outside the quotes

=COUNTIF(E2:E9,>2) is a syntax error. Use ">2", or ">"&G1 to compare against a cell.

Using COUNTIF in a workbook for Excel 2003

COUNTIFS arrived in Excel 2007. Anyone on 2007 or later, including Excel for the web and Mac, has both, so this only matters for very old files saved as .xls.

FAQ

Is COUNTIFS slower than COUNTIF?

Not in any way you will notice on normal sheets. With one condition they do the same work. Use whichever reads more clearly.

Can COUNTIFS have just one condition?

Yes. =COUNTIFS(D2:D9,"Open") is valid and returns the same result as COUNTIF. Many people use COUNTIFS everywhere for consistency.

How many conditions can COUNTIFS take?

Up to 127 range and criteria pairs. In practice, if you need more than four or five, a PivotTable or FILTER is easier to read.

Do COUNTIF and COUNTIFS support wildcards?

Yes, both. "*lamp*" matches any text containing lamp and "T-10??" matches T-10 followed by exactly two characters. Neither is case-sensitive.

Functions used

Related guides

Updated