SUMIFS Function

Adds up the cells that meet several conditions.

Category

Math & Trig

Available in

Excel 2010+, Microsoft 365

Returns

A number

Taught hands-on in Level 3: Intermediate Lookups & Data Summaries · Conditional Summaries module

Overview

SUMIFS totals the rows where every condition is true — West region and Product A and this month.

Note the order: the range to add comes first, then pairs of criteria range and criteria.

Syntax

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

Arguments

ArgumentRequired?What it does
sum_rangeYesThe cells to add.
criteria_range1YesThe first range to test.
criteria1YesThe condition for criteria_range1.
criteria_range2, criteria2, …OptionalUp to 126 more range/condition pairs.

Examples

Example 1 — Region and product

Total Widgets sold in the West.

E2fx=SUMIFS(C2:C6, A2:A6, "West", B2:B6, "Widget")
ABCDE
1RegionProductSalesWest Widgets
2WestWidget6201050
3EastWidget480
4WestGadget500
5WestWidget430
6NorthWidget350
=SUMIFS(C2:C6, A2:A6, "West", B2:B6, "Widget") Explain

Example 2 — Between two dates

Use the same date column twice — once for the start, once for the end.

=SUMIFS(C2:C100, A2:A100, ">="&F1, A2:A100, "<="&F2) Explain

Common mistakes

  • “OR” conditions. SUMIFS only does AND. For West or East, add two SUMIFS together, or use =SUM(SUMIFS(C2:C6, A2:A6, {"West","East"})).

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.