Use SUMIFS to sum cells that match multiple criteria in Excel
The SUMIFS function in Excel allows you to sum the values in a range of cells that meet multiple criteria. For example, you might use the SUMIFS function in a sales spreadsheet to to add up the value of sales of a specified product by a given sales person (e.g. the value of all sales of a microwave oven made by John). This lesson shows you how to use SUMIFS in your own spreadsheets.
Note that SUMIFS was introduced in Excel 2007. This lesson therefore only applies to Excel 2007, 2010, 2011 and 2013.
Also, you'll find a number of examples in the comments at the end of the lessons where people have been trying to use SUMIFS when a Pivot Table is a better solution. If you can't find what you want here, try our lessons on Pivot Tables instead (opens in a new tab).
Using SUMIFS - an example
Consider the following table that contains sales data:
Imagine you want to add up the sales amounts that are greater than $500, and where the quantity sold was 3 or more. You could solve the problem by creating an additional column and use the IF function to evaluate the sales amount and the sales quantity and calculate whether or not to include that sales amount in the total, but that would be somewhat long-winded AND would mean you have to have another column in the table. But the SUMIFS function means you don't have to.
The SUMIFS() function syntax
The SUMIFS() function has the following syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2,....)
The function arguments are as follows:
- sum_range is the range of cells to be added up
- criteria_range1 is the first range of cells that are to be checked for a criteria match
- criteria1 is the criteria against which criteria_range1 is to be checked.
- criteria_range2 is the second range of cells that are to be checked for a criteria match
- criteria2 is the criteria against which criteria_range2 is to be checked.
You must have at least one criteria range and criteria. However, if you only have one criteria range, then you may as well use the SUMIF() function instead (it works in almost exactly the same way as the SUMIFS function).
SUMIFS lets you have up to 127 different sets of criteria_range and criteria. Note that the shape of each criteria_range must be the same as the shape of the sum_range. This isn't necessary with the SUMIF function.
The SUMIFS() function in action
Let's use the SUMIFS function to solve the scenario introduced at the start of this lesson.
- The table above now looks like this (I've included the SUM function so you can see the overall sales total for the table:
- As you'll see, B41 is showing the total sales, but only for those days where the quantity sold was greater than 3 (criteria1) and the sales amount was greater than $500 (criteria2). The two rows that meet both of these criteria in the table above are Thursday and Friday. The SUMIFS formula I used to perform this calculation is shown in cell C41 so you can see how it works.
- Remember that, as with the SUMIF function, when you're using a criteria such as "greater than", you need to use quotation marks around the criteria, i.e. ">3" and ">500".
- The same applies if you wanted to use a text criteria, such as the day of the week. An example of a text criteria might be "Thursday" if you only wanted sales made on Thursday (useful if you have a spreadsheet like this that spans several weeks).
I've included a downloadable copy of the spreadsheet above, which also includes the SUMIF examples based on the same sales table. You can use this to try out different variations on the example above. Try changing the values in the Quantity and the Sales Amount columns, and check that the formula works correctly. Then, as a stretch, try adding a third criteria range to the formula so that it only adds sales for Thursday.
Did this lesson help you understand the SUMIFS() function? Do you have an alternative scenario where you've used the SUMIFS function? Tell us about it by writing a comment below.