Jun 2, 2026

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

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.

See what people say

"Before Carly, I relied on a Calendly link, but the whole process felt impersonal and not very professional. Carly changed that by handling all the back-and-forth, so I'm no longer stuck in endless email threads trying to line up schedules.

Now Carly reaches out to candidates, shares my real-time availability, lets them pick a slot, then sends a Zoom link and drops it straight into my calendar. She sends reminders to both of us before each call, which has significantly reduced no-shows and last-minute confusion.

On top of scheduling, Carly acts like a full executive assistant, sending me my schedule the night before so I can prepare for each call. It reminds me of the old x.ai assistant, but Carly is noticeably smarter, faster, and better suited to my healthcare recruitment business."

Gus Ibrahim, Founder & Director, IHR