Use SUMIF with multiple criteria in Excel

The SUMIF function allows you to add up the values in a range of cells in Excel that meet a certain criteria. However, in some cases the SUMIF function isn't quite enough, and you need the SUMIFS function instead. This lesson explains how to use SUMIFS.

SUMIF is a very useful function, which I explained here. In a nutshell, it lets you add up a range of cells that meet a certain criteria. For example, you could add up all of the cells in a range that have a value that is $500 or greater.

But sometimes scenarios arise where you need to be able to add up only those cells in a range that meet several different criteria. This is where SUMIFS comes in handy (note that SUMIFS was introduced in Excel 2007 - if you're using an older version of Excel you won't be able to follow along with this tutorial).

Consider the following table:

Example of a scenario in Excel where SUMIFS will help you add up sales that meet multiple criteria.

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 then you may as well use the SUMIF() function instead.

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:
    Excel - a worked example of the SUMIFS function
  • 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). I've highlighted the two rows that meet both of these criteria in the table above (Thursday and Friday). The formula I used is in 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 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.