Excel SUMIF Function
SUMIF adds values that match one condition.
Use it for one-condition totals, such as revenue for one region, hours for one project, or expenses above a threshold.
SUMIF syntax & arguments
Syntax
=SUMIF(range, criteria, [sum_range])
-
1
range
RequiredThe cells to test against the criterion.
-
2
criteria
RequiredThe condition that decides which rows or cells are included. It can be a number, text, cell reference, expression such as
">100", or wildcard pattern. -
3
sum_range
OptionalThe cells to add when the matching cell in
rangemeets the criterion. If omitted, Excel adds the matching cells inrange.
Example
=SUMIF(B2:B50, "East", D2:D50)
Add values in D2:D50 where the matching cell in B2:B50 is East.
SUMIF caveats
SUMIF is useful for simple conditional totals, but alignment and criteria syntax matter.
-
Range alignment matters
rangeandsum_rangeshould cover the same rows or columns so the tested cells line up with the values being added. -
Operators usually need quotes
Criteria such as
">100"or"<>Cancelled"must be written as text. -
It only handles one condition
Use SUMIFS instead when the total depends on multiple criteria, such as region and month.
Need to count instead? Use COUNTIF when matching cells should be counted rather than summed.
Intro SUMIF practice problem
Solve the intro problem directly here, or open it on its own page.
Advanced SUMIF practice problems
Use SUMIF alongside other Excel functions in realistic, less-prescriptive challenges.
