Explanation of the SUMIF function
The SUMIF function is asking for 3 arguments as shown below:
Example data used to explain how the SUMIF 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. range (1st argument)
The SUMIF function will use the range (or ‘criteria range’) that you provide to find a match against your criteria. The range that you provide can be either an entire column or a range.
2. criteria (2nd argument)
This is the criteria that you want to look for in the range (or criteria range). This will determine which of the numbers from the [sum_range] get added up into the final result.
- if any values in the range you provide do not match your criteria –> the corresponding values from the [sum_range] are ignored
- if the values in the range you provide match your criteria –> the corresponding values from the [sum_range] are added up
3. [sum_range] (3rd argument)
The [sum_range] is what the SUMIF function will use to add up the numbers whenever it finds a match for your criteria.
How does the SUMIF function work?
You should use the SUMIF function when you need the sum of a set of numbers only for a certain criteria. When your criteria is found within the range you provide, it will sum up the corresponding numbers in your [sum_range].
The SUMIF function starts with the 1st value in the range (or criteria range) that you provide and checks to see if it matches the criteria you provided.
- if yes, it includes the value from the [sum_range] into the final sum
- if not, it ignores the value from the [sum_range]
It does this evaluation for every value within your range or (criteria range) and sums all of the values from the [sum_range] where it found a match for your criteria.
Keep in mind the SUMIF function syntax when reading the Formulas shown below… =SUMIF(range,criteria,[sum_range])
Explanation of results
- Shirts sold in location A = 3 + 13 + 11 + 16 + 15 + 2 + 8 + 19 + 6 = 93
- White shirts sold = 16 + 15 + 2 + 9 + 17 + 19 = 78
- Medium shirts sold = 13 + 15 + 19 + 2 + 17 + 2 = 68
Go back to Excel Tutorials.