SUMIFS Function in Excel: Multi-Criteria Sum Guide

Need to sum values that match more than one condition? SUMIFS is the go-to function for totaling numbers based on multiple criteria — dates, categories, regions, and more — all in a single formula.

SUMIF vs SUMIFS

Feature	SUMIF	SUMIFS
Criteria	Single condition	Multiple conditions
Syntax order	Range, criteria, sum_range	Sum_range, criteria_range1, criteria1, …
Compatibility	All versions	Excel 2007+

Recommendation: Always use SUMIFS, even with one condition. It is more flexible and the syntax is more consistent.

Basic Syntax

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

Parameter	Description
sum_range	The cells to add up
criteria_range1	The range to evaluate for the first condition
criteria1	The condition that must be met
Additional pairs	Optional extra criteria_range/criteria pairs

Simple Example

Given this sales data:

A (Rep)	B (Region)	C (Amount)
Alice	East	5,000
Bob	West	3,200
Alice	West	4,100
Carol	East	6,800
Bob	East	2,900

Sum all sales by Alice:

=SUMIFS(C:C, A:A, "Alice")

Result: 9,100 (5,000 + 4,100)

Sum sales by Alice in the East region:

=SUMIFS(C:C, A:A, "Alice", B:B, "East")

Result: 5,000

Using Comparison Operators

Wrap operators in double quotes:

Criteria	Meaning
">1000"	Greater than 1,000
"<500"	Less than 500
">=100"	Greater than or equal to 100
"<>"	Not blank
"<>Cancelled"	Not equal to “Cancelled”

Example: Sum amounts greater than 3,000 in the East region:

=SUMIFS(C:C, B:B, "East", C:C, ">3000")

Result: 6,800

Working with Dates

SUMIFS handles date ranges elegantly. Given a Date column in D:

Sum sales in March 2026:

=SUMIFS(C:C, D:D, ">="&DATE(2026,3,1), D:D, "<="&DATE(2026,3,31))

Sum sales from a specific date onward:

=SUMIFS(C:C, D:D, ">="&DATE(2026,1,1))

Key point: Use the & operator to concatenate the comparison operator with the DATE function. Do not put DATE inside quotes.

Using Wildcards

SUMIFS supports two wildcards in text criteria:

Wildcard	Meaning	Example
*	Any number of characters	"*Corp" matches “ABC Corp”
?	Exactly one character	"A??" matches “A01”, “A99”

Example: Sum sales for all reps whose name starts with “A”:

=SUMIFS(C:C, A:A, "A*")

To search for a literal asterisk or question mark, prefix with a tilde: "~*" or "~?".

Cell References in Criteria

Instead of hardcoding values, reference cells for dynamic formulas:

=SUMIFS(C:C, A:A, F1, B:B, F2)

Where F1 contains the rep name and F2 contains the region. This makes your formulas reusable and easy to update.

With operators and cell references:

=SUMIFS(C:C, D:D, ">="&F1, D:D, "<="&F2)

SUMIFS with Multiple Columns: A Real-World Scenario

Imagine a budget tracking sheet:

Dept	Category	Month	Actual	Budget
Sales	Travel	Jan	2,500	3,000
Sales	Software	Jan	1,200	1,000
HR	Travel	Jan	800	1,500
Sales	Travel	Feb	3,100	3,000

Total actual travel spend for Sales in Jan:

=SUMIFS(D:D, A:A, "Sales", B:B, "Travel", C:C, "Jan")

Result: 2,500

Total budget overruns for Sales (where Actual > Budget): This requires a helper column or an array approach — SUMIFS alone cannot compare two columns row-by-row. Use SUMPRODUCT instead:

=SUMPRODUCT((A2:A100="Sales")*(D2:D100>E2:E100)*(D2:D100-E2:E100))

Common Mistakes

1. Wrong argument order — SUMIFS puts sum_range first, unlike SUMIF which puts it last.
2. Mismatched range sizes — All ranges must be the same size. C2:C100 and A2:A50 will cause an error.
3. Dates as text — If dates are stored as text, SUMIFS date comparisons will fail. Ensure your date column uses proper date formatting.
4. Forgetting quotes around operators — >1000 without quotes causes an error. Always write ">1000".

Quick Reference

Task	Formula
Single criterion	=SUMIFS(C:C, A:A, "Alice")
Two criteria	=SUMIFS(C:C, A:A, "Alice", B:B, "East")
Greater than	=SUMIFS(C:C, C:C, ">5000")
Date range	=SUMIFS(C:C, D:D, ">="&DATE(2026,1,1), D:D, "<="&DATE(2026,12,31))
Wildcard	=SUMIFS(C:C, A:A, "A*")
Not blank	=SUMIFS(C:C, A:A, "<>")

Key Takeaways

SUMIFS is one of the most practical Excel functions for everyday analysis. It handles multiple conditions cleanly, works well with dates and wildcards, and pairs naturally with cell references for dynamic reports. Master SUMIFS and you will rarely need complex array formulas for basic conditional aggregation.