SUMIF vs SUMIFS in Excel

Use SUMIF when you need to total values that match one condition. Use SUMIFS when you need to total values that match two or more conditions.

Quick examples:

=SUMIF(B2:B8,"East",E2:E8)

=SUMIFS(E2:E8,B2:B8,"East",D2:D8,"Paid")

The first formula totals East region sales. The second totals sales where Region is East and Status is Paid.

If you want the full syntax and worked examples for each function, use the Excel SUMIF and SUMIFS tutorial. This guide is for choosing the right formula quickly. If you only need a basic total without conditions, start with the Excel SUM function.

1. SUMIF vs SUMIFS: Quick Difference

Both functions add up values based on criteria, but their formula patterns are different.

The practical rule is simple:

In newer workbooks, SUMIFS is often the better default because it can handle one condition now and more later. SUMIF is still shorter and easier to read when the question is truly single-condition.

2. Example Data

Suppose you have this sales table:

We will use this table for the examples below.

3. Use SUMIF for One Condition

SUMIF has two required parts and one optional total range:

=SUMIF(range, criteria, [sum_range])

To total paid sales:

=SUMIF(D2:D8,"Paid",E2:E8)

Result: 2450

Excel checks D2:D8 for Paid, then adds the matching values from E2:E8.

Other common SUMIF examples:

Use SUMIF when one range and one rule are enough.

4. Use SUMIFS for Multiple Conditions

SUMIFS uses the sum range first, then pairs of criteria ranges and criteria:

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

To total East region sales that are already paid:

=SUMIFS(E2:E8,B2:B8,"East",D2:D8,"Paid")

Result: 1630

Only rows where both conditions are true are included:

This is the key difference: SUMIFS applies AND logic. Every condition must match on the same row.

5. SUMIFS Can Also Handle One Condition

You can use SUMIFS with just one criteria pair:

=SUMIFS(E2:E8,B2:B8,"East")

That returns the same result as:

=SUMIF(B2:B8,"East",E2:E8)

So why not always use SUMIFS?

For simple sheets, shorter is fine. For dashboards and templates, consistency often matters more.

6. Criteria Examples for Text, Numbers, and Dates

Both functions use the same criteria style.

Text Criteria

Exact text needs quotation marks:

=SUMIF(B2:B8,"East",E2:E8)

Wildcards help with partial text:

=SUMIF(C2:C8,"*Pro*",E2:E8)

The asterisk means any number of characters before or after Pro.

Number Criteria

Comparison operators also go inside quotation marks:

=SUMIF(E2:E8,">=500")

If the threshold is in a cell, join the operator and the cell reference:

=SUMIF(E2:E8,">="&H2)

Date Criteria

Date ranges usually need SUMIFS because there are two conditions: on or after the start date, and on or before the end date.

Formula:

=SUMIFS(E2:E8,A2:A8,">="&H2,A2:A8,"<="&H3)

Result: 2400

For a date range plus another condition, add another criteria pair:

=SUMIFS(E2:E8,A2:A8,">="&H2,A2:A8,"<="&H3,B2:B8,"East")

Result: 2090

7. SUMIF vs SUMIFS for Duplicate or Repeated Records

If a record should be added only when one field matches, SUMIF is enough:

=SUMIF(A2:A100,A2,E2:E100)

If the total depends on more than one field, use SUMIFS:

=SUMIFS(E2:E100,A2:A100,A2,B2:B100,B2)

For the counting version of this idea, see COUNTIF vs COUNTIFS in Excel.

8. SUMIF vs SUMIFS for List Matching

When matching one list against another, SUMIF can total values from a lookup list:

=SUMIF($D$2:$D$100,A2,$E$2:$E$100)

Use SUMIFS when the match depends on more than one field:

=SUMIFS($E$2:$E$100,$D$2:$D$100,A2,$F$2:$F$100,B2)

This is useful for region-specific totals, product-specific revenue, and other reports where one label is not enough. For a broader lookup-style comparison, see How to Compare Two Columns in Excel.

9. Common SUMIF and SUMIFS Mistakes

Mistake 1: Reversing the Argument Order

SUMIF checks the criteria range first. SUMIFS starts with the sum range.

=SUMIF(B2:B8,"East",E2:E8)

=SUMIFS(E2:E8,B2:B8,"East")

When a formula returns zero unexpectedly, confirm that the ranges are in the right positions.

Mistake 2: Using Ranges with Different Sizes

Every range in SUMIFS must have the same shape.

=SUMIFS(E2:E8,B2:B7,"East")

This is wrong because the ranges are different sizes. Use matching ranges:

=SUMIFS(E2:E8,B2:B8,"East")

Mistake 3: Forgetting Quotation Marks Around Operators

Excel needs comparison operators as text criteria:

=SUMIF(E2:E8,">500")

When the threshold is in a cell, combine the operator and the cell reference:

=SUMIFS(E2:E8,E2:E8,">"&H2)

Mistake 4: Numbers Stored as Text

If amounts are stored as text, the formula may return unexpected results. You can test this by selecting the cells and checking whether Excel shows a warning indicator or aligns the values differently.

Common fixes include:

• Use Data > Text to Columns and finish the wizard without changing settings
• Multiply the range by 1 in a helper column
• Use VALUE in a helper formula before summarizing

Mistake 5: Expecting SUMIFS to Mean OR

SUMIFS uses AND logic. If you want East or West totals, add separate formulas:

=SUMIF(B2:B8,"East",E2:E8)+SUMIF(B2:B8,"West",E2:E8)

10. Practice Example

Using the sample table above, answer these questions:

Reference answers:

11. Summary

Use SUMIF for a single condition and SUMIFS for multiple conditions. Choose SUMIFS when a formula may grow from one condition to several, when you are building dashboards, or when you need date ranges. Remember that SUMIFS starts with the sum range, all criteria ranges must be the same size, and comparison operators like >= need quotation marks.

12. Related Guides

• Learn the full syntax in the Excel SUMIF and SUMIFS tutorial.
• Review basic totals in the Excel SUM function.
• Compare the count version of this pattern with COUNTIF vs COUNTIFS in Excel.
• Return row-level labels with IF with AND OR in Excel.
• Use Excel IF Function Tutorial when you need a label instead of a total.
• Return the matching rows with Excel FILTER Function Tutorial.