The SUMIFS function adds numbers from a range based on one or more matching conditions. This guide explains how SUMIFS works and includes practical examples you can use immediately.
WHAT THE SUMIFS FUNCTION DOES
SUMIFS totals values that meet multiple criteria simultaneously, using AND logic — all conditions must be satisfied for a value to be included. It lets you sum data that meets all the criteria you provide, such as summing sales where the region is North and the product is A.
WHEN TO USE SUMIFS
Use SUMIFS when you want to:
- Sum values using one or more filters
- Apply multiple conditions to your data
- Avoid manual filtering or helper columns
Tip: Make sure your sum_range and all criteria_range values are the same size.
FORMULA SYNTAX
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)| Argument | Description |
|---|---|
sum_range | Range of numbers to total |
criteria_range1 | Range to test against first condition |
criteria1 | The first condition to match |
criteria_range2 | (optional) Another range to test |
criteria2 | (optional) Second condition to match |
EXAMPLE 1: ONE CONDITION
Sum sales in the East region.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Product | Sales |
| 2 | East | A | 100 |
| 3 | West | A | 200 |
| 4 | East | B | 150 |
| 5 | North | A | 250 |
=SUMIFS(C2:C5, A2:A5, "East")Result: 100 + 150 = 250
Need help applying this? Try this in Numstro Builder
EXAMPLE 2: TWO CONDITIONS
Sum sales where Region = North and Product = A.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Product | Sales |
| 2 | East | A | 100 |
| 3 | West | A | 200 |
| 4 | East | B | 150 |
| 5 | North | A | 250 |
=SUMIFS(C2:C5, A2:A5, "North", B2:B5, "A")Result: 250 (only row 5 matches both)
Need help applying this? Try this in Numstro Builder
COMMON MISTAKES & EDGE CASES
- All ranges —
sum_rangeand allcriteria_ranges — must be the same size. Mismatches will return#VALUE! - If no rows match all conditions, the result is
0(not an error) - Text and logical conditions must be enclosed in quotes, e.g.
"North",">100" - Blank cells in
sum_rangeare ignored, not treated as zero - SUMIFS uses AND logic — all conditions must be met in the same row
- Wildcards like
"*"(any characters) and"?"(single character) are supported in text criteria - Use
<>to exclude a specific value (e.g."<>West"excludes rows where Region is West)
RELATED FUNCTIONS
- SUMIF — sum with one condition
- COUNTIFS — count with multiple conditions
- AVERAGEIFS — average with multiple conditions
FURTHER READING
Official Microsoft documentation:
SUMIFS — Microsoft Support