SUMIF
Adds values in a range that meet a specified condition.
SUMIF(range, criteria, [sum_range])Arguments
rangeThe range to evaluatecriteriaThe condition to meetsum_rangeThe range to sum (defaults to range)(optional)
=SUMIF(A1:A10,">100")Sums values greater than 100
=SUMIF(A1:A10,"Apple",B1:B10)Sums B where A is 'Apple'
=SUMIF(A:A,"*sale*",B:B)Uses wildcard matching
- •Use SUMIFS for multiple conditions
- •Wildcards * and ? work in criteria
- •Criteria is not case-sensitive
- •Trying to squeeze a second condition into SUMIF - it only ever takes one range/criteria pair, no exceptions. SUMIFS is the door out
- •Giving sum_range a different size than range, which shifts the whole sum onto the wrong rows without any error message
- •Typing SUMIF(A1:A10,>100) without quotes around the operator - Excel won't sum a thing until it's ">100"
SUMIFSThe moment you need a second condition, this is where you're headedCOUNTIFSame single-condition idea, just counting matches instead of summing themSUMPRODUCTMore flexible when your conditional sum needs logic that SUMIF simply can't expressCan SUMIF handle more than one condition?
No, not even close - it's built for exactly one range against exactly one criterion. The second you need a second condition, that's SUMIFS territory.
Why is sum_range optional in SUMIF?
Leave it out and SUMIF just sums the same range it's testing. You only need to specify it when the values you're summing live in a different column than the ones being checked.
Can SUMIF handle dates with comparison operators?
Yes - SUMIF(A:A,">="&DATE(2026,1,1),B:B) sums column B for every row where the date in column A falls on or after January 1, 2026.
Need to translate a formula using SUMIF?
Use our translator to convert your complete formula