NumstroFUNCTIONSPLAYGROUND
WHAT DOES SUMIFS DO

The SUMIFS function adds numbers from a range based on one or more matching conditions. It lets you sum data that meets all the criteria you provide.

For example, you can sum sales where the region is North and the product is A.

WHEN TO USE IT
  • You need to sum values using one or more filters
  • Your data is structured in rows or columns
  • You want a cleaner alternative to 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], ...)
ArgumentDescription
sum_rangeRange of numbers to total
criteria_range1Range to test against first condition
criteria1The 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.

ABC
1RegionProductSales
2EastA100
3WestA200
4EastB150
5NorthA250
=SUMIFS(C2:C5, A2:A5, "East")

Result: 100 + 150 = 250

EXAMPLE 2: TWO CONDITIONS

Sum sales where Region = North and Product = A.

ABC
1RegionProductSales
2EastA100
3WestA200
4EastB150
5NorthA250
=SUMIFS(C2:C5, A2:A5, "North", B2:B5, "A")

Result: 250 (only row 5 matches both)

COMMON PITFALLS AND EDGE CASES
  • All ranges — sum_range and all criteria_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_range are 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
FURTHER READING

For advanced usage and edge cases, see the official documentation:
SUMIFS – Microsoft Support