SUMIFS
Adds values that meet multiple criteria. More powerful than SUMIF for complex conditions.
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Arguments
sum_rangeThe range to sumcriteria_range1First range to evaluatecriteria1First conditioncriteria_range2Additional range(optional)criteria2Additional condition(optional)
=SUMIFS(C:C,A:A,"Sales",B:B,">1000")Sums C where A is 'Sales' AND B>1000
=SUMIFS(D:D,A:A,A1,B:B,">="&C1)Uses cell references for criteria
=SUMIFS(C:C,A:A,"*Product*")Sums where A contains 'Product'
- •Sum range comes FIRST (unlike SUMIF)
- •All criteria must be met (AND logic)
- •Use wildcards * and ? in text criteria
- •Putting sum_range last out of pure SUMIF habit - SUMIFS wants it first, and this is the single most common typo people carry over
- •Giving the criteria ranges different sizes, which throws a #VALUE! error with little explanation
- •Expecting OR logic between conditions. SUMIFS only does AND - every single pair has to match, not just one of them
SUMIFThe simpler version for one condition, with sum_range in a different spotCOUNTIFSSame multi-condition structure, just counting matching rows instead of summing a valueSUMPRODUCTWhat you actually need once OR logic enters the picture, since SUMIFS won't do itCan SUMIFS do OR logic instead of AND between conditions?
Not on its own - every criteria pair has to be true at the same time. Want OR logic? Add several SUMIFS together, one per condition, or restructure the problem with SUMPRODUCT.
Why does SUMIFS put sum_range first when SUMIF puts it last?
SUMIF was built around a single condition, so sum_range could be optional and tucked at the end. SUMIFS supports as many conditions as you need, so sum_range became mandatory and moved to the front for consistency.
Can SUMIFS filter by a date range?
Yes - SUMIFS(C:C,A:A,">="&DATE(2026,1,1),A:A,"<="&DATE(2026,12,31)) sums column C for every date falling within 2026.
Need to translate a formula using SUMIFS?
Use our translator to convert your complete formula