COUNTIF vs COUNTIFS: What's the Difference?

The difference in COUNTIF vs COUNTIFS is the number of conditions. COUNTIF counts cells that meet one condition; COUNTIFS counts rows that meet several conditions, all at the same time. COUNTIFS with a single condition does exactly what COUNTIF does, so the real question is when you need more than one. This comparison is for beginners who have used COUNTIF and want to know when to switch.

COUNTIF vs COUNTIFS at a glance#

Both count, both use the same criteria rules, and both work in every modern version of Excel. The difference is how many conditions they accept.

How the syntax differs#

COUNTIF has two parts: where to look and what to match. COUNTIFS repeats that pair as many times as you need.

=COUNTIF(range, criteria)
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)

The criteria work the same way in both:

• Text goes in quotes: "Open".
• A number needs no quotes: 2.
• A comparison goes in quotes with the operator: ">=8", "<>Low" (not equal to Low).
• A cell needs no quotes: G1. To compare with a cell, join it: ">"&G1.
• Wildcards: * for any characters, ? for one character: "M*" matches Maya.

Our guide to COUNTIF with criteria examples covers every type, and the COUNTIF function guide and COUNTIFS function guide list the arguments.

The same counts, side by side#

Here is a small support-ticket log. Both functions answer the questions below on this data.

Task 1: one condition (how many tickets are open?)#

=COUNTIF(C2:C8,"Open")
=COUNTIFS(C2:C8,"Open")

Both return 4. With one condition they are identical.

Task 2: two conditions (how many High tickets are open?)#

COUNTIF can't do this in one step. COUNTIFS can:

=COUNTIFS(B2:B8,"High",C2:C8,"Open")

The result is 2 (rows 2 and 6). Row 5 is High but Closed, so it isn't counted: every pair must match.

Task 3: a between count (tickets that took 3 to 6 hours)#

COUNTIFS puts two conditions on the same column. With COUNTIF, you count "3 or more" and subtract "more than 6":

=COUNTIFS(D2:D8,">=3",D2:D8,"<=6")
=COUNTIF(D2:D8,">=3")-COUNTIF(D2:D8,">6")

Both return 4 (3, 6, 4 and 5 hours). The COUNTIFS version reads more naturally.

Task 4: OR logic (tickets that are High or Medium)#

COUNTIFS conditions are always AND, so neither function does OR in one call. Add two counts instead:

=COUNTIF(B2:B8,"High")+COUNTIF(B2:B8,"Medium")

The result is 5 (3 High + 2 Medium). This works because one cell can't be both. SUMIFS with OR logic shows the same idea for totals.

Task 5: one formula for a whole summary#

Real reports count the same thing for every agent. Put the names in a column and point the criteria at a cell instead of typing the name. With Maya, Leo and Sam in F2:F4, write this in G2 and fill down:

=COUNTIFS($A$2:$A$8,F2,$C$2:$C$8,"Open")

The results are 2, 1 and 1. The data ranges are locked with $ so they stay put, while F2 moves to F3 and F4. COUNTIF can do the same for a single condition, =COUNTIF($A$2:$A$8,F2), which counts all tickets per agent: 3, 2 and 2.

Task 6: partial matches with wildcards#

Both functions accept wildcards in text criteria. * stands for any number of characters:

=COUNTIF(A2:A8,"M*")

The result is 3, the three Maya tickets. Excel wildcards in COUNTIF and SUMIF explains ? and how to search for a real asterisk.

When to use COUNTIF#

Use COUNTIF for quick, single-condition counts. It is the natural choice when:

• You have one question about one column, such as "how many are overdue?".
• You want the shortest formula for a summary row or a quick check.
• You're building a check for duplicates: =COUNTIF($A$2:$A$100,A2)>1 flags repeated IDs. See find and highlight duplicates.
• Someone else will maintain the sheet and only knows COUNTIF.

When to use COUNTIFS#

Use COUNTIFS whenever there is more than one condition. It is the better choice when:

• Conditions are on different columns, such as agent and status, or department and rating.
• You need a between count on dates or numbers, such as all orders in March: =COUNTIFS(A2:A50,">="&DATE(2026,3,1),A2:A50,"<"&DATE(2026,4,1)).
• The criteria come from cells, so one formula can be filled across a summary grid. Our guide to COUNTIFS and SUMIFS with multiple conditions builds one.
• You might add a condition later. Starting with COUNTIFS means you only add a pair, not rewrite the formula.

Headcount by department and grade is a classic HR use; see Excel skills for HR and recruiting jobs.

Common mistakes with COUNTIF and COUNTIFS#

Most wrong counts come from a few small slips:

1. Ranges of different sizes in COUNTIFS. =COUNTIFS(A2:A8,"Maya",C2:C9,"Open") returns #VALUE!. Every range must cover the same rows.
2. Missing quotes around operators. =COUNTIF(D2:D8,>=3) is a syntax error. Write ">=3".
3. Putting a cell reference inside the quotes. ">G1" looks for text that is literally ">G1". Write ">"&G1.
4. Expecting OR from COUNTIFS. =COUNTIFS(B2:B8,"High",B2:B8,"Medium") returns 0, because no cell is both.
5. Hidden spaces in the data. "Open " with a trailing space doesn't match "Open". Clean the column with TRIM, or use "Open*".
6. Counting blanks by accident. "<>Closed" also counts empty cells. Add C2:C8,"<>" as a second pair to skip them; see count non-blank cells with a condition.

Do COUNTIF and COUNTIFS work in all versions of Excel?#

COUNTIF works in every version. COUNTIFS was added in Excel 2007, so it works in every version you're likely to meet today, including Excel 2016, 2019, 2021, 2024, Microsoft 365, Excel for Mac and Excel for the web. Version is not a reason to choose one over the other.

If you want to see the matching rows rather than count them, use FILTER; our comparison of XLOOKUP vs FILTER explains when that fits. For turning codes into labels before you count them, see CHOOSE vs SWITCH.

Our recommendation#

Learn COUNTIF first, because it teaches the criteria rules that every "IF" function shares. Then use COUNTIFS as your everyday counting function: it handles one condition just as well, and it grows when the question gets harder.

In a job test, either is accepted for a single condition. For two or more conditions, COUNTIFS is the answer graders expect, and a between count with two pairs on one column shows you understand it well.

In short: COUNTIF for one quick condition, COUNTIFS for everything else.

Practise it#

Count Maya's open tickets in the exercise on this page. Then work through the COUNTIF lesson, which covers text, numbers, operators and cell criteria, and the COUNTIFS and SUMIFS lesson for multiple conditions.