What is SUMIFS with Multiple Criteria?
Summing up values in excel based on conditions is the kind of logical calculation we do to get the sum based on condition. To perform these logic-based calculations we have a variety of functions in excel. When you want to sum values based on more than one criteria then we need to use SUMIFS formula in excel. In this article, we will guide you on how to use SUMIFS formula with multiple criteria’s.
SUMIFS Formula in Excel
SUMIFS is the improved version formula of SUMIF function. SUMIFS allows us to match multiple criteria to sum any range of values. For example, if you have sale values based on city-wise across multiple months then using SUMIFS function we can get the total sales value for the particular city in the particular month. In this case, City & Month are the criteria to arrive at the sales value.
So, when the criteria to arrive the result is single we can use SUMIF and in case of more than one criteria, we can use SUMIFS function.
Below is the syntax of the SUMIFS formula.
- Sum Range: This is simply the range of cells that we need to sum.
- Criteria Range 1: To Sum Range what is the criteria range.
- Criteria 1: From the Criteria Range 1 what is the one particular value we need to sum?
- Criteria Range 2: To Sum Range what is the second criteria range.
- Criteria 2: From the Criteria Range 2 what is the one particular value we need to sum?
Like this, we can give 127 criteria ranges to sum one particular value.
How to Use SUMIFS with Multiple Criteria?
Below are examples of how to use the SUMIFS formula with multiple criteria.
Example #1
For example, look at the below sales data.
From the above table, we need to find “what is the total sales for the city “Florida” and for the month of “Aug”.
- Open SUMIFS function in the I2 cell.
- The first argument of SUMIFS function is Sum Range i.e. what is the column we need to sum, so in this case, we need to sum the “Sales” column so select the range of values from E2 to E16.
- The second argument is Criteria Range 1 i.e. based on what criteria we need to sum “Sales” column. In this case, our first criteria are to sum values based on the “State” column, so fir this argument select A2 to A16 cells.
- After mentioning the Criteria Range 1 we need to mention what is the Criteria 1 value from the selected Criteria Range 1. In this range we need the sum value of the state “Florida” so we have this state value in G2 cell, give the cell reference.
- Now we need to select the second Criteria Range 2 so our second criteria range is to sum value is “Month” so select the cells from D2 to D16.
- After mentioning the Criteria Range 2 we need to mention what is the Criteria 2 value from the selected Criteria Range 2. In this range we need the sum value of the month “Aug” so we have this state value in H2 cell, give the cell reference.
- Ok, we are done with supplying all the criteria. Close the bracket and hit enter to get the result.
So for the city “Florida” and for the month, “Aug” total sales is $1,447. So SUMIFS function first looks for the city “Florida” and in this city, it looks for the month of “Aug” and whichever the rows match these two criteria are summed up.
Example #2
Now for the same data, we will see how to use more criteria. For example for the same state “Florida” and for the month of “Aug” and for the sales rep “Peter” we need to find the sales value.
- For the old formula, we need to add one more criteria i.e. “Sales Rep” criteria of “Peter”.
- For the Criteria Range 3 choose the “Sales Rep” cell values.
- After selecting the Criteria Range 3 column we need to mention the Criteria 3 i.e. we need only the sum of the “Sales Rep” “Peter”, so give cell reference as I6 cell.
- Ok, third criteria are also supplied so close the bracket and hit enter key to get the result.
The only one-row item matches the criteria of “State = Florida”, “Month = Aug”, and “Sales Rep = Peter” i.e. row number 5 (colored with green).
Things to Remember
- SUMIFS formula can match 127 criteria’s.
- Cell reference length should be the same for all the parameters of the formula.
Recommended Articles
This has been a guide to SUMIFS with Multiple Criteria. Here we discuss how to sum values based on more than one criteria using SUMIFS Formula in Excel. You can learn about excel from the following articles –
- Examples of Using Break-Even Analysis in Excel
- Examples to Use Excel Convert Function
- Using SUMIFS with Dates
- Using Sumif Text in Excel
- Use SumIf with Multiple Criteria
- Use SUMIF With VLOOKUP
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
