Excel SUMIFS: A Step-by-Step Tutorial on How to Use the SUMIFS Function

Microsoft Office is now installed on over one billion desktops worldwide, so it’s no surprise that Microsoft Excel is the top spreadsheet application for data manipulation and analysis. You can also attribute its widespread popularity to the 400+ built-in functions that make data analysis easier to perform. 

Rest assured that you don’t have to learn the entire catalog of statements to become an Excel pro, but it is a good idea to brush up on the essentials to understand the spreadsheet program at an advanced level. You can look at our overview of the most important Excel statements that all professionals should know in our blog. 

In this article, though, we will look specifically at the Excel SUMIFS function and how to use it to gain better control over your data.

Excel SUMIFS example

This tutorial will show you how to use the SUMIFS function to sum data according to various criteria you choose. We will use fictitious sales data from a fake hardware store for our spreadsheet. The data contains various items sold, the salesperson who sold the items, and the value of the items sold.

Let’s take a look at our initial data:

Assume we’re in charge of managing inventory for the company, and the sales manager has asked us to extract information from the spreadsheet. He would like to know the following:

•      How many nails did the store sell for the day?

•      How many hammers did John sell?

•      What is the value of the bolts Harry sold in each sale that included over two bolts?

The SUMIFS function is one of the best ways to extract this type of data from our spreadsheet. We can actually use it for all the information the sales manager is asking for.

How to use the SUMIFS function in Excel

Accessing functions in Excel couldn’t be simpler. All you have to do is click on the cell where you want the answer to appear, and then either type in the function’s name or select the function from the Formulas tab.

So in doing that with the SUMIFS function, the cell that you just selected will show the sum of all data within a selected range based on several different criteria.

To get a better understanding of how that works, we should know the syntax associated with the SUMIFS function and how it works. The syntax is as follows:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Typing the SUMIFS function within a cell or selecting from the menu initiates the function. In other words, by typing SUMIFS in the cell, Excel knows it must use the SUMIFS function. Once you have triggered the SUMIFS function, you must add parameters. Here is the description of the parameters the SUMIFS function accepts:

•      sum_range: This is the range of the data that you want to find the sum of.

•      criteria_range1: This is the range of the cells that you want to use as a filter by applying criteria1 against the data in them.

•      criteria1: This is the criteria that the range of cells in criteria_range1 are filtered by before the values are summed.

•      criteria_range2, … criteria_range_n: Optional. This is the range of cells that you want to apply criteria2, … criteria_n against. You can include up to 127 different ranges for your SUMIFS function.

•      criteria2, … criteria_n: Optional. This is useful for determining which cells to add. criteria2 will apply against criteria_range2, criteria3 will apply against criteria_range3, and so on. You can include up to 127 different criteria for your SUMIFS function.

Now that we have a good grasp of how the function works, let’s apply the SUMIFS function to our fictitious data.

Using a single set of range and criteria in Excel SUMIFS

To use SUMIFS to figure out all nails the store sold for the day, you need to select the cell you want the answer to appear in first. So select D19 and click on the fX button. You should then see the Formula Builder appear on the right side of the Excel interface. The SUMIFS function is part of the Math & Trig functions available in Excel, so you can select Math & Trig and then click on the SUMIFS function, or you can just search in the Formula Builder with “SUMIFS” and click on the function when it appears. This will open up a wizard that you can use to enter your criteria for the function.

What we want to do is add up all of the nails that every sales clerk sold, so we should first set Sum_range to the cell references D2:D17. After that, we can look at the amount column to determine how many nails each salesperson sold. Now we know to set criteria_range1 as B2:B17. If we type “Nails” into Criteria1, Excel will search for every entry where “Nails” appears and sum the number of nails that each salesperson was able to sell. 

Remember, we chose D19 as the cell where our results will appear. Look at cell D19 in our image below to see how many nails the store was able to sell that day:

Using two sets of range and criteria in Excel SUMIFS

Our next example will use two parameters. We need to tell Excel to SUM all of John’s hammer orders. 

To do this, you must use the SUMIFS function again, selecting your target cell first and then selecting the formulas tab. Select the Math & Trig functions and then select SUMIFS from the drop-down menu. Once again, the SUMIFS function wizard will open, allowing you to create the criteria for your SUMIFS function.

Here is what the completed wizard will look like:

The Sum_range is once again the units sold because we want Excel to count how many hammers John sold. The first criteria range is the stock type and the criteria to search for this time is “Hammer.” The second criteria we want Excel to search for is the salesman criteria. The range is C2:C17, and the criteria we need to enter is John.

Excel will now SUM all units that they sold that were hammers and that were sold by John. The output of the above function is 4.

Using three sets of range and criteria in Excel SUMIFS

This example will use three search criteria. Use the steps above to open the function wizard and then add the following search criteria to the wizard:

•      Add Sum_range of E2:E17 to sum the value of the sale.

•      Criteria_range1 is the Stock range, so enter B2:B17.

•      Type “Hammer” into Criteria1 to search for all Hammers sold.

•      Criteria_Range2 is the salesman range, so enter C2:C17.

•      Criteria2 is Harry because we want to search for sales Harry made.

•      Criteria_range3 is D2:D17 because we want to search for sales Harry made where he sold more than five units per sale.

•      Criteria3 is “>2” because we are searching for sales made that were greater than two units per sale.

This is what the results of our SUMIFS function for the above criteria look like:

SUMIFS filter criteria

We went as far as three sets of filter ranges and criteria. If you want to experiment with more sets of filter ranges and criteria, try adding them yourself. It may help to add more columns to our sample spreadsheet so you have more ranges that you can filter by. You can add sales dates, sales tax, company names, and brand if you want to get creative.

So far, we have only compared the cells in the spreadsheet using an exact match and the greater than operator. This will only get you so far. Now let’s review those filter criteria and look at other types of filter criteria you can use to perfect your SUMIFS formulas.

Numeric criteria

When you use an exact value as your filter criteria, Excel will sum only those values that equal that value in the range you chose for the criteria. For example, if we wanted to know the total of sales where the customer only purchased one item, the formula would look like this:

SUMIFS(E2:E17, D2:D17, 1)

We have two sets of range arguments. For the first argument, we have the range of the sales data in the spreadsheet. The second argument is the range of sales in the Amount column we are going to filter by. And the last parameter of 1 says we only want to sum the sales column value if the amount column value is equal to 1.

Because the criteria parameter is compared with the equal sign, just typing the value you want to compare by works, but if you noticed that when we were searching for sales that were greater than two, we had to use “>2” with the double quotes surrounding the value. You will have to do this when you filter by any numeric criteria that you are not just trying to match. So if we wanted to filter by sales that are less than or equal to 3, then we would use the following formula:

SUMIFS(E2:E17, D2:D17, "<=3")

You can also set the criteria for another cell. This would allow us to change the formula by changing the value in the cell instead of modifying our formula every time we want to test a new value. Here is an example of what that formula would look like:

SUMIFS(E2:E17, D2:D17, G2)

But let’s say that we wanted to sum the values in the range only if the total was less than or equal to another cell value. This is where we not only have to use double quotes but also the ampersand operator. So here is the last formula, modified to filter by lesser than or equal to another cell’s value.

SUMIFS(E2:E17, D2:D17, "<=" & G2)

Text criteria

In the examples above, we filtered sales by the name of the person who sold them when we filtered by Tom and Harry. Because we were filtering by text strings, we had to surround these names with double-quotes. It would seem that that is about all you could do with text, but there are so many other possibilities for filtering text values.

Let’s say you want to sum all the sales where the salesperson wasn’t Harry. You would need to use the <> operator, which means “does not equal.” Here is an example of a formula using this operator:

SUMIFS(E2:E17, C2:C17, "<>Harry")

We have to use double quotes around the whole criteria in this case.

But what if we only know a few characters of the criteria we want to filter by? Let’s say that we wanted to sum all the sales by a salesperson whose name started with H and consisted of five characters. This made-up example fits our toy spreadsheet, but it is also helpful in the real world. For this, you can use the ? operator. This operator matches exactly one character, and this can be any character. So to return the same results as our last example, but use this new operator, we can use this formula:

SUMIFS(E2:E17, C2:C17, "H????")

That formula works, but is a little lengthy. This is where the * operator comes in. This operator matches a series of zero or more characters. So we could replace the last formula with the next to sum sales by those sold by salespeople whose name starts with H:

SUMIFS(E2:E17, C2:C17, "H*")

Since this operator matches zero or more characters, this formula would also filter by salespeople who only have their initial H in the column.

But what if we want to sum the sales by either John or Harry? This doesn’t require an extra operator, just more thinking about the problem. What is another way to describe the problem? We want to add the sum of Harry’s sales to the sum of John’s sales. This gives us another option. We can get this result with the following formula:

SUMIFS(E2:E17, C2:C17, "Harry") + SUMIFS(E2:E17, C2:C17, "John")

We simply add the two SUMIFS formulas together.

Date criteria

If you want to use a date as the criteria to filter the values you sum, then nothing we have covered so far will work. Let’s say we added another column called Date that has the date of our sales in it. This will be column F. If we want the filter to match only July 15th of 2020, this is the formula we could use:

SUMIFS(E2:E17, F2:F17, DATE(2020, 4, 15))

The DATE function in Excel accepts three arguments. The first is the year, the second is the month, and the third is the day of the month.

Another option is to filter the values you want to sum by today’s sales date. You can do this using Excel’s TODAY function, like this example:

SUMIFS(E2:E17, F2:F17, TODAY())

If you want to use more than an exact match for your criteria, you will have to use double quotes in combination with an ampersand operator, just like we did with numeric data. So if you wanted to sum the sales data from sales that happen before today, the formula would look like this:

SUMIFS(E2:E17, F2:F17, "<" & TODAY())

Excel SUMIFS notes

We have looked at quite a few examples of how to use the Excel SUMIFS function. Here are some pointers on using SUMIFS for review:

•      It adds values based on multiple criteria.

•      Blank values and text in the sum range are ignored by the formula.

•      The criteria you use can be a number, expression, cell reference, text, or another formula.

•      If you are using criteria that are text or mathematical symbols (like =,+,-,/,*), then you should use double-quotes.

•      You can use wildcard characters in the criteria.

•      Criteria cannot be longer than 255 characters.

•      Cells used for the sum_range parameter are added only when all the conditions are met.

•      Cells used for the sum_range parameter that contain TRUE evaluate to 1.

•      Cells in sum_range that contain FALSE evaluate to 0 (zero).

•      The range of the cells used for sum_range and all the criteria_range must be equal.

Conclusion

The Excel SUMIFS function is a really powerful function for adding the values of cells in your spreadsheets based on multiple filter criteria. This one function can replace multiple sets of SUM and IF functions which will not only require more formulas, but also involve complicated nesting that can become confusing relatively quickly. The Excel Formula builder makes using SUMIFS even simpler by providing a wizard to walk you through using it. Now that you know how to use the Excel SUMIFS function, let’s move on to SUMPRODUCT. You can also add even more to your Excel skills by learning how to create a pivot table in Excel and what you can do with VBA in your spreadsheets.