Quick Navigation

SUMIFS Function

Excel 2007+

Summary

The SUMIFS function sums values in a specified range that meet multiple criteria across different ranges. It's perfect for complex data analysis where you need to filter and sum based on several conditions simultaneously.

Syntax

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Parameters

Parameter	Type	Required	Description
sum_range	`Range`	Yes	Cells containing values to sum when all criteria match
criteria_range1	`Range`	Yes	First criteria range to check
criteria1	`Any`	Yes	First criteria defining which rows to include
criteria_range2	`Range`	No	Additional criteria range (up to 127 pairs)
criteria2	`Any`	No	Additional criteria

Using the SUMIFS Function

SUMIFS excels at multi-condition aggregation. Unlike SUMIF (single criteria), SUMIFS starts with sum_range first, followed by criteria pairs. Use it for sales reports, budget analysis, or any scenario requiring filtered totals across multiple dimensions.

Common SUMIFS Examples

Sum products starting with 'A' sold by Tom

=SUMIFS(A2:A9,B2:B9,"=A*",C2:C9,"Tom")

Sums quantities where products begin with 'A' AND salesperson is Tom. Returns 20.

Sum non-banana sales by Tom

=SUMIFS(A2:A9,B2:B9,"<>Bananas",C2:C9,"Tom")

Sums quantities excluding bananas sold by Tom. Returns 30.

Monthly sales above target

=SUMIFS(Sales[Amount],Sales[Date],">="&DATE(2024,1,1),Sales[Date],"<=&DATE(2024,1,31),Sales[Amount],">1000")

Sums January sales over $1000.

Frequently Asked Questions

Up to 127 range/criteria pairs.

SUMIF uses one criteria (sum_range last). SUMIFS uses multiple criteria (sum_range first).

Yes, sum_range and all criteria_ranges must have identical dimensions.

Common Errors and Solutions

Returns 0 instead of expected result

Cause: Text criteria missing quotes

Solution: Wrap text criteria in quotes: "Tom", "North"

Incorrect results with TRUE/FALSE in sum_range

Cause: TRUE=1, FALSE=0 automatically

Solution: Convert booleans to numbers first or exclude them

#VALUE! error

Cause: Mismatched range sizes

Solution: Ensure all ranges have same rows/columns

Notes

sum_range first, then criteria pairs
Wildcards: *=any characters, ?=single character, ~=literal * or ?
Text criteria need quotes, numbers usually don't
All range arguments must match in size
Or logic requires multiple SUMIFS added together

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Excel 365

Not available in: Excel 2003, Excel XP, Excel 2000, Excel 97

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+