SUMIF vs SUMIFS: What's the Difference?

The short answer to SUMIF vs SUMIFS: SUMIF adds numbers that meet one condition, and SUMIFS adds numbers that meet one or more conditions at the same time. They also take their arguments in a different order, which is the most common source of mistakes. This guide is for anyone who builds sales, expense or HR totals and wants to pick the right one quickly.

SUMIF vs SUMIFS at a glance#

SUMIFS can do everything SUMIF does, plus more conditions, but the range you add moves to the front.

How the syntax differs#

Both functions check cells against a condition and add the matching numbers. The difference is how many conditions they accept and where the numbers to add go.

=SUMIF(range, criteria, [sum_range])
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• range or criteria_range is the column to check, such as the Region column.
• criteria is what the cells must match: "North", ">=4" or a cell like G1.
• sum_range is the column of numbers to add.

In SUMIF, sum_range is optional. If you leave it out, SUMIF adds the cells it checked: =SUMIF(D2:D9,">=4") adds every unit count of 4 or more. In SUMIFS, sum_range is always required and always comes first. The SUMIF function guide and the SUMIFS function guide cover each argument.

The same total solved both ways#

Here is a small sales list:

Task 1: one condition (total North revenue)#

Both functions return 6,300 (3,600 + 500 + 1,200 + 1,000):

=SUMIF(A2:A9,"North",E2:E9)
=SUMIFS(E2:E9,A2:A9,"North")

Same ranges, same condition, different order. In SUMIF the revenue range is last. In SUMIFS it is first.

Task 2: two conditions (North and Laptop)#

SUMIF can't check two columns at once. SUMIFS adds a second pair:

=SUMIFS(E2:E9,A2:A9,"North",C2:C9,"Laptop")

The result is 4,800: rows 2 and 6 are the only North laptop orders. Row 4 is North but a Monitor, so it is left out.

Task 3: a condition on numbers#

Total Kofi's revenue from orders of 4 or more units:

=SUMIFS(E2:E9,B2:B9,"Kofi",D2:D9,">=4")

The result is 5,800 (4,800 + 1,000). Kofi's 3-unit order in row 2 doesn't pass ">=4". Put comparison operators inside the quotes with the number.

Task 4: a range between two values#

You can check the same column twice. This adds orders with 2 to 5 units:

=SUMIFS(E2:E9,D2:D9,">=2",D2:D9,"<=5")

The result is 13,550. Only the 1-unit and 6-unit orders are left out.

When to use SUMIF#

Use SUMIF when you have exactly one condition and want the shortest formula. It fits well when:

• You total one category, such as all Travel expenses or all North sales.
• The condition is on the numbers you add, such as every claim over 250: =SUMIF(D2:D12,">250"), with no sum range needed.
• You're working in a file that already uses SUMIF and want to keep one style.
• You need a quick check in a test. "What were total Laptop sales?" is one condition, and =SUMIF(C2:C9,"Laptop",E2:E9) returns 12,000 in seconds.

When to use SUMIFS#

Use SUMIFS whenever you have two or more conditions, and consider it your default even for one. It is the better choice when:

• You filter by more than one column, such as region and product, or department and month.
• You need a range of values, such as dates within a month or units between 2 and 5.
• You want mistakes to show. If one range is a row short, SUMIFS returns #VALUE! instead of a quietly wrong total.
• You build summary tables that may gain more conditions later. Adding a pair to SUMIFS is easier than rewriting a SUMIF.

Totals like these are everyday work in many roles; see the Excel skills for sales operations jobs and the Excel skills for HR jobs.

How to use cells, wildcards and OR conditions#

Criteria don't have to be typed into the formula. If G1 holds North and G2 holds 4:

=SUMIFS(E2:E9,A2:A9,G1,D2:D9,">="&G2)

This returns 1,000, the only North order with 4 or more units. The & joins the operator ">=" to the value in G2. Writing ">=G2" inside the quotes would compare against the text "G2" and return 0.

SUMIFS only does AND logic. For North or South, add two SUMIFS:

=SUMIFS(E2:E9,A2:A9,"North")+SUMIFS(E2:E9,A2:A9,"South")

The result is 9,950 (6,300 + 3,650). This works because a row can't be in both regions, so nothing is added twice.

Both functions also accept wildcards in text criteria: * means any number of characters and ? means any one character. This adds every product whose name starts with "Lap":

=SUMIFS(E2:E9,C2:C9,"Lap*")

The result is 12,000, the same as "Laptop" here, but it would also catch "Laptop Pro" or "Laptop stand" if the list had them. Dates work like numbers: to add sales from 2026 onwards in a date column, use a criterion such as ">="&DATE(2026,1,1). Typing the date as text inside the quotes can fail when the reader's date settings are different, so DATE is the safer habit.

Common mistakes with SUMIF and SUMIFS#

These are the slips that most often give wrong totals:

1. Putting the sum range last in SUMIFS. When you switch from SUMIF, move the range to add to the front.
2. Adding a second pair to SUMIF. =SUMIF(A2:A9,"North",E2:E9,C2:C9,"Laptop") gives a "too many arguments" message. Rewrite it as SUMIFS.
3. Leaving operators outside the quotes. Write ">=4", not >=4.
4. Putting a cell reference inside the quotes. Write ">="&G2, not ">=G2".
5. Ranges of different sizes. SUMIF uses only the top-left cell of its sum range and stretches it to match the checked range. So =SUMIF(A2:A9,"North",E2:E3) still adds E2:E9 and returns 6,300. That is harmless here, but if the sum range starts on the wrong row, the total is wrong without warning. In SUMIFS, C2:C10 next to A2:A9 returns #VALUE!, so you notice.
6. Numbers stored as text. Cells that look like numbers but are text are skipped, so the total is too low.
7. Not locking ranges before filling down. Use $E$2:$E$9 (press F4 on Windows or ⌘T on a Mac) before you fill with Ctrl+D (⌘D on a Mac).

Counting works the same way: COUNTIF and COUNTIFS follow the same pattern. If you count rather than add, our guide to COUNT vs COUNTA vs COUNTBLANK covers the basic counting functions. And if your totals feed a formula that can fail, see IFERROR vs IFNA.

Our recommendation#

Learn SUMIF first, because its order (check, match, add) is easy to understand. Then make SUMIFS your everyday choice: it handles one condition or many, its argument order never changes, and it shows #VALUE! when your ranges don't line up. Use SUMIF when you see it in an existing file or want a quick one-condition total.

In short: SUMIFS by default, SUMIF for quick single-condition totals.

Practise it#

Try the SUMIFS exercise on this page: it needs two conditions, so SUMIF can't do it. Then practise SUMIF in a live spreadsheet and move on to the COUNTIFS and SUMIFS lesson to build multi-condition totals with locked ranges.