How to Use SUMIFS in Excel: Syntax, Example, and Tips

The SUMIFS function in Excel adds numbers that meet multiple criteria across ranges. You specify the sum range first, then pairs of criteria ranges and their conditions to filter which values to add.

Syntax

The SUMIFS function syntax is:

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

• sum_range: The cells to add up.
• criteria_range1: The range to check for the first condition.
• criteria1: The condition to apply to criteria_range1.
• criteria_range2, criteria2, ...: Optional additional ranges and conditions.

Each criteria filters which cells in the sum range are included in the total.

excel

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

Example

This example sums sales amounts for "Apples" in the "East" region.

Suppose you have:

• Column A: Product
• Column B: Region
• Column C: Sales

The formula sums sales where product is "Apples" and region is "East".

excel

A1: Product
A2: Apples
A3: Oranges
A4: Apples

B1: Region
B2: East
B3: West
B4: East

C1: Sales
C2: 100
C3: 150
C4: 200

Formula in D1:
=SUMIFS(C2:C4, A2:A4, "Apples", B2:B4, "East")

Output

300

Common Pitfalls

Common mistakes when using SUMIFS include:

• Ranges not being the same size. All sum_range and criteria_range arguments must have the same number of rows and columns.
• Using incorrect criteria syntax, like forgetting quotes around text criteria.
• Mixing relative and absolute references incorrectly when copying formulas.

Example of a wrong formula and the fix:

excel

Wrong:
=SUMIFS(C2:C4, A2:A3, "Apples", B2:B4, "East")

Right:
=SUMIFS(C2:C4, A2:A4, "Apples", B2:B4, "East")

Quick Reference

Part	Description	Example
sum_range	Cells to sum	C2:C10
criteria_range1	Range to check first condition	A2:A10
criteria1	Condition for criteria_range1	"Apples"
criteria_range2	Optional second range	B2:B10
criteria2	Condition for second range	"East"

Key Takeaways

SUMIFS sums values based on multiple conditions across ranges.

All ranges must be the same size to avoid errors.

Text criteria need quotes, numbers do not.

Use SUMIFS to filter sums by more than one rule easily.

Check your ranges and criteria carefully to avoid mistakes.