Jun 2, 2026

How to Use SUMIF in Excel (with Examples, 2026)

Excel Sumif Spreadsheets How-to Formulas Productivity

SUMIF adds up the numbers in a range, but only for rows that meet a single condition — total sales for one region, expenses over a threshold, hours for one project. Here’s the syntax, common criteria patterns, and how to step up to multiple conditions.

SUMIF Syntax

=SUMIF(range, criteria, [sum_range])

range — the cells to evaluate against the condition.
criteria — the condition, e.g. "Apple", ">100", or a cell like F1.
sum_range — (optional) the cells to actually add. If omitted, Excel sums range itself.

1. Sum by a Text Match

Column A has regions, column B has sales. To total sales for “West”:

=SUMIF(A2:A100, "West", B2:B100)

Excel checks column A for “West” and adds the matching values from column B.

2. Sum With a Comparison

Use operators inside the quotes to sum by size. To add every value in B greater than 100:

=SUMIF(B2:B100, ">100")

Here sum_range is omitted, so Excel sums the same column it tests. Other operators: ">=", "<", "<>" (not equal).

3. Reference a Cell in the Criteria

To make the condition dynamic, combine an operator with a cell reference using &:

=SUMIF(B2:B100, ">"&E1)

Now changing the threshold in E1 updates the total automatically.

4. Use Wildcards

* matches any number of characters; ? matches a single character. To sum sales where the product name contains “Pro”:

=SUMIF(A2:A100, "*Pro*", B2:B100)

5. Multiple Conditions: SUMIFS

When you need more than one condition, switch to SUMIFS — note the sum_range comes first:

=SUMIFS(C2:C100, A2:A100, "West", B2:B100, ">100")

This sums column C only where region is “West” and the value in B is over 100. Add as many range, criteria pairs as you need.

6. Troubleshooting

Result is 0

The criteria didn’t match anything. Check for extra spaces, and make sure numbers aren’t stored as text (a green triangle in the corner of a cell is the tell).

#VALUE! error

range and sum_range are different sizes. Make both the same height (e.g., both 2:100).

Comparison isn’t working

Operators must be inside quotes: ">100", not >100. When referencing a cell, concatenate: ">"&E1.

SUMIFS argument order

Unlike SUMIF, SUMIFS puts the sum range first, then each criteria_range, criteria pair.

Ready to automate your busywork?

Carly schedules, researches, and briefs you—so you can focus on what matters.

Get Carly Today →

Or explore our free tools