How to Use SUMIF in Excel (with Examples)
SUMIF adds up only the numbers that meet a condition. The syntax is =SUMIF(range, criteria, [sum_range]) — like totaling sales for one region. Here is exactly how each part works, plus SUMIFS and wildcards.
SUMIF is the function you reach for when a plain SUM is not enough — when you want to add only the numbers that meet a condition. Total the sales for one region, add only the expenses over a certain amount, sum the hours for one project: SUMIF does all of it with a single, readable formula.
The SUMIF syntax, explained
=SUMIF(range, criteria, [sum_range])| Part | Meaning |
|---|---|
| range | The cells to test against your condition |
| criteria | The condition to match, like "North" or ">100" |
| sum_range | The cells to add up (optional — if left out, Excel adds the range itself) |
A real example
Say column A holds regions and column B holds sales, in the range A2:B100. To total the sales for the North region, you write:
=SUMIF(A2:A100, "North", B2:B100)This checks A2:A100 for "North" and adds the matching values from B2:B100. Wrap text criteria in quotation marks. You can also test numbers directly — =SUMIF(B2:B100, ">100") adds every value greater than 100 (no sum_range needed, because you are summing the same column you are testing).
Partial matches with wildcards
SUMIF understands two wildcards in text criteria: an asterisk (*) for any number of characters and a question mark (?) for a single character. To sum every product code that starts with "AB":
=SUMIF(A2:A100, "AB*", B2:B100)More than one condition: SUMIFS
When you need two or more conditions — say the North region AND sales over 100 — use SUMIFS. Note that it puts the sum range first:
=SUMIFS(B2:B100, A2:A100, "North", B2:B100, ">100")SUMIF pairs naturally with other tools. Combine it with an IF function for extra logic, summarize the results in a pivot table, or do the same task in Google Sheets with SUMIF in Google Sheets.
Frequently asked questions
What is the difference between SUMIF and SUMIFS in Excel?
SUMIF adds values that meet one condition, with the sum range last. SUMIFS handles several conditions at once and puts the sum range first. Use SUMIF for a single test and SUMIFS when two or more must all be true.
How do I use SUMIF with a date range?
Use SUMIFS with two date conditions, like =SUMIFS(B2:B100, A2:A100, ">="&DATE(2026,1,1), A2:A100, "<="&DATE(2026,3,31)) to total values within a quarter. The & joins the operator to the date so Excel reads it correctly.
Why is my SUMIF returning 0?
Usually the criteria does not match — a trailing space, different text, or numbers stored as text. Check that the range and criteria line up, and that "North" in the data exactly matches the "North" in your formula.