Excel SUMIF and SUMIFS Tutorial: Add Values That Match Your Criteria

Introduction

When a worksheet grows beyond a simple list, SUM is usually not enough. You may need to total only sales from one region, only expenses from this month, or only rows where the status is complete. That is where SUMIF and SUMIFS are useful.

SUMIF adds numbers that match one condition. SUMIFS adds numbers that match multiple conditions. This guide walks through the syntax, practical examples, date and text criteria, common mistakes, and a simple rule for choosing the right function.

1. SUMIF vs SUMIFS: Which Function Should You Use?

Both functions calculate a conditional total, but they are designed for different levels of filtering.

In modern Excel workbooks, SUMIFS is often the better default because it can handle one condition or many conditions with the same structure. Still, SUMIF is common in older files and is worth understanding.

2. SUMIF Syntax

The SUMIF function checks one range for a condition, then adds matching values from a sum range.

=SUMIF(range, criteria, [sum_range])

• range: The cells Excel checks against the condition
• criteria: The rule to apply, such as "East", ">=500", or "*Pro*"
• sum_range: Optional. The cells to add when the condition is true

If you omit sum_range, Excel adds the values in range itself.

Example Data

Suppose you have this sales table:

To total sales from the East region:

=SUMIF(B2:B6,"East",E2:E6)

Result: 2090

Excel checks B2:B6 for East, then adds the matching values from E2:E6.

3. SUMIFS Syntax

The SUMIFS function puts the sum range first, then accepts pairs of criteria ranges and criteria.

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

• sum_range: The numeric cells to add
• criteria_range1: The first range to test
• criteria1: The first condition
• criteria_range2 / criteria2: Optional additional condition pairs

The order is different from SUMIF, which is the most common source of errors when switching between the two functions.

To total East region sales that are already paid:

=SUMIFS(E2:E6,B2:B6,"East",D2:D6,"Paid")

Result: 1340

Only rows where Region is East and Status is Paid are included.

4. Use Text Criteria

Text criteria can be exact matches or wildcard matches.

Wildcards are useful when the cell contains extra words:

=SUMIFS(E2:E6,C2:C6,"*Pro*")

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

5. Use Number Criteria

Comparison criteria must be wrapped in quotation marks.

For example, to total all sales of at least 500:

=SUMIFS(E2:E6,E2:E6,">=500")

Result: 2350

When the comparison value is stored in a cell, join the operator and cell reference with &:

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

If H2 contains 500, the formula sums values greater than or equal to 500.

6. Sum Between Two Dates

Date criteria are easier to maintain when you store the start and end dates in cells.

To sum sales in January:

=SUMIFS(E2:E6,A2:A6,">="&H2,A2:A6,"<="&H3)

This pattern is reliable because the date values live in cells instead of being typed directly into the formula.

You can also combine dates with other filters. To sum paid East sales in January:

=SUMIFS(E2:E6,A2:A6,">="&H2,A2:A6,"<="&H3,B2:B6,"East",D2:D6,"Paid")

7. Sum Blank or Non-Blank Rows

SUMIFS can also filter based on blanks.

This is helpful for workflow sheets where a blank status means the item is not processed yet.

=SUMIFS(E2:E6,D2:D6,"")

If the cells look blank but are not matching, check for hidden spaces or formulas that return an empty string.

8. 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:B6,"East",E2:E6)

=SUMIFS(E2:E6,B2:B6,"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:E6,B2:B5,"East")

This formula is wrong because E2:E6 has five cells and B2:B5 has four.

Use matching ranges:

=SUMIFS(E2:E6,B2:B6,"East")

Mistake 3: Forgetting Quotation Marks Around Operators

Excel needs comparison operators as text criteria.

=SUMIFS(E2:E6,E2:E6,">500")

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

=SUMIFS(E2:E6,E2:E6,">"&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

9. SUMIFS vs PivotTables

SUMIFS is best when you need a specific number inside a report layout, such as a monthly dashboard or a management summary. PivotTables are better when you want to explore data interactively.

Many real workbooks use both: a PivotTable for exploration and SUMIFS formulas for the final dashboard.

10. Practice Example

Using the sample table above, answer these questions:

Reference answers:

Conclusion

Use SUMIF when you only need a quick one-condition total. Use SUMIFS when you want a consistent formula pattern that can grow from one condition to many. The main things to remember are simple: SUMIFS starts with the sum range, all criteria ranges must be the same size, and comparison operators belong inside quotation marks.

Once you are comfortable with these patterns, you can build reliable summaries for sales, budgets, attendance, inventory, project tracking, and almost any other list-based Excel report.