How to Use SUMIFS in Excel
By Glenn Romano Here is a great tip that many people are not currently aware of and can be a useful tool to keep in your Excel tool belt—SUMIFS. The SUMIFS function was introduced in Excel 2007 and was carried over into Excel 2010 and 2013.
This tool is used to total up multiple sets of numbers based on a criteria you specify.
For example: here is a large set of data that we are going to evaluate and sum all sales figures that match our two sets of criteria: Month & Store Number. We can do this once for sales and apply the same function to find the total number of units sold for the same set of criteria.
The SUMIFS function syntax looks like this:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)
We will be referencing column D (Sales) as our Sum_Range, column A (Months) as our Criteria_range1, and cell H5 as our Criteria1. For our second set of Criteria or Criteria_range2 we will reference column B (Store#) and our Criteria2 will be cell I5. Employees will type what they are looking for in cells H5 and I5 and the SUMIFS function will evaluate the data and return an answer.
TIP #1 – You can have up to 127 range/criteria pairs.
TIP #2 - Only one set is required to receive an answer.
TIP #3 – Items that are bold in Function Argument dialogue boxes are required fields.
TIP #4 – The SUMIFS function is found in the Math & Trig button.
So let’s complete the simple steps to apply the SUMIFS function to this large data set.
Step 1: Click in cell J5 where we will want the answer to our SUMIFS function to appear.
Click on the Formulas Tab>>Function Library group>>Math & Trig Button – Scroll down the list of functions and select SUMIFS.
Step 1 Continued: Once selected the SUMIFS dialogue box will appear.
Step 2: Complete our SUMIFS Function – First click on column label D (Sales) for our Sum_Range, then click in the next field and click on column label A (Months) for our Criteria_range1, then click cell H5 for our Criteria1. Continue the steps for Criteria_range2 by selecting column label B (Store#) and then click cell I5 for our Criteria 2.
The end result will look like this. As you can see we already have an answer prior to even clicking on the OK button.
All sales figures matching the criteria month of “Jan” and the criteria Store# of “1000” have been totaled and our answer is $155,904.00. Cells H5 and I5 can now be used to type any month and store number respectively to find total sales matching the multiple criteria that you desire.
We can repeat the same steps to figure out the total number of units for these two sets of criteria as well. The only difference being choosing column label E “Units” for the Sum_range column.
As you can see we have come up with a result of 5,568 total units for the multiple criteria of month “Jan” and store# “1000".
Here is what the final answers look like for both Sales Totals and Unit Totals.
Sales Totals =SUMIFS(D:D,A:A,H5,B:B,I5)
Unit Totals =SUMIFS(E:E,A:A,H5,B:B,I5)
I hope you find this tip useful and start experimenting using SUMIFS to evaluate multiple criteria sets to total the results of your own data.
Glenn Romano has been a corporate trainer for Fortune 500 companies for 15 years. He teaches the Office Suite at Learn iT! and has been known to toss out Jolly Ranchers to reward his enthusiastic students.