Explanation of the SUMIFS function
The SUMIFS function allows you to evaluate for multiple criteria with the following argument structure:
- criteria_range1, criteria1
- criteria_range2, criteria2
- Note: You can input up to a max of 127 [criteria_range, criteria]
In the image below I purposely typed a few commas to force the argument description to show multiple criteria.
- SUMIF –> evaluates for only 1 criteria
- SUMIFS –> evaluates for multiple criteria. Note: You can still use the SUMIFS function if you only have 1 criteria. In this way it will act like a SUMIF function.
Example data used to explain how the SUMIFS function works
- 3 different colors
- each color has 3 different sizes
- 2 locations (color coded to easily distinguish them apart)
- Quantity sold at each of the 2 locations
1. sum_range (1st argument)
The sum_range is what the SUMIFS function will use to add up the numbers whenever it finds a match for your criteria in the critera_ranges.
2. criteria_range1, criteria1 (2nd and 3rd arguments)
The SUMIFS function will use the criteria_ranges to find a match against your criteria for each pair of [criteria_range, criteria] that you provide.
Note: You can input up to 127 pairs of [criteria_range,criteria].
How does the SUMIFS function work?
You should use the SUMIFS function if you need the sum of a set of numbers when multiple criteria are true.
When the function finds a match for each criteria in the corresponding criteria_ranges (which is evaluated for each pair of [criteria_range,criteria]), it will add up the corresponding numbers from the sum_range into the final result.
For each row within your data set:
- If at least 1 of the criteria does not match the value from its criteria_range –> the corresponding value from the sum_range is ignored
- If each criteria matches the value from its criteria_range –> the corresponding value from the sum_range is added up
Keep in mind the SUMIFS function syntax when reading the Formulas shown below… =SUMIFS(sum_range,criteria_range1,criteria1,….<all the way to>….,criteria_range127,criteria127)
Explanation of results
- Blue shirts sold in location A = 8 + 19 + 6 = 33
- White and Large shirts sold in location B = 19
- Black and Medium shirts sold = 13 + 2 = 15
- All shirts sold in location B = 18 + 2 + 15 + 9 + 17 + 19 + 12 + 2 + 1 = 95
Go back to Excel Tutorials.