Sometimes we may need to get a summation based on different criteria. For this purpose, Excel provides a function called SUMIFS. This is the updated version of the SUMIF function. In this article, we will show you 6 effective ways to use SUMIFS for multiple criteria in different columns. If you are also curious to know, download our practice workbook and follow us.
Use SUMIFS with Multiple Criteria in Different Columns: 6 Suitable Examples
To demonstrate the approaches, we consider a dataset of 11 items, and their vendors’ names, quantities, and prices are mentioned here. So, our dataset is in the range of cells B5:E15.
📚 Note:
All the operations of this article are accomplished by using the Microsoft Office 365 application.
1. Apply SUMIFS Function with Comparison Operators
In the first example, we are going to compare with a given data and sum all the values greater than that value using the SUMIFS function. Our criteria value is in cell H4, and the comparison criteria are in cell H5.
The steps to finish this example are given below:
📌 Steps:
- First of all, select cell H6.
- Now, write down the following formula in the cell.
=SUMIFS(D5:D15,C5:C15,H4,D5:D15,H5)
- Press Enter.
- You will get the sum result.
Thus, we can say that our formula works perfectly, and we are able to use the SUMIFS function for multiple criteria for different columns.
Read More: Excel SUMIFS Not Equal to Multiple Criteria
2. Using SUMIFS Function with Date Value
In the second example, we will use the SUMIFS and TODAY functions to add all the values based on our desired date. For that, we will add a new column in column F, entitled Delivery Date. Our vendor criteria value will be in cell I4. The steps to complete this example are given as follows:
📌 Steps:
- First, select cell I5.
- After that, write down the following formula in the cell.
=SUMIFS(D5:D15, F5:F15,">="&TODAY()-20, F5:F15,"<="&TODAY(),C5:C15,I4)
- Now, press Enter.
- It will show you zero (0) in cell I5.
- Then, write down the vendor criteria in cell I4. Here, we wrote David.
- You will notice that the formula sums up the quality of the product which lies within our time limit.
Hence, we can say that our formula works effectively, and we are able to use the SUMIFS function for multiple criteria for different columns.
3. Applying SUMIFS Function with Blank Cells
In the following example, the SUMIFS function will help us to sum values based on the blank cell remaining in the dataset. We have 4 entities for vendor David. Among them, 2 delivery dates are blank cells.
The steps to accomplish this example are shown below:
📌 Steps:
- At first, select cell I5.
- Then, write down the following formula inside the cell.
=SUMIFS(D5:D15, F5:F15,"=",C5:C15,I4)
- Afterward, press Enter, and you will get a 0 value in that cell.
- Now, write down David as the vendor criteria in cell I4.
- You will see that the formula will sum up the quality of the product for the blank cells, and the other two cells will be omitted.
Therefore, we can say that our formula works precisely, and we are able to use the SUMIFS function for multiple criteria for different columns.
Read More: Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria
Similar Readings
- Excel SUMIFS with Multiple Vertical and Horizontal Criteria
- How to Use SUMIFS When Cells Are Not Equal to Multiple Text
- SUMIFS with Multiple Criteria Along Column and Row in Excel
4. Utilizing SUMIFS Function with Multiple OR Criteria
Here, we are going to use the SUMIFS function to perform the OR operation with multiple criteria. Our desired criteria are in the range of cells I4:I6.
The steps to perform the operation are described below:
📌 Steps:
- Firstly, select cell I7.
- Next, write down the following formula inside the cell.
=SUMIFS(D5:D15,C5:C15, I5, F5:F15,">=7/1/2021", F5:F15, "<=7/31/2021") + SUMIFS(D5:D15, C5:C15, I6, F5:F15, ">=7/1/2021", F5:F15, "<=7/31/2021")
- Now, press Enter.
- You will get your desired result.
So, we can say that our formula works fruitfully, and we are able to use the SUMIFS function to perform multiple OR operations from multiple criteria.
🔎 Breakdown of the Formula
We are breaking down the formula for cell I7.
👉 SUMIFS(D5:D15,C5:C15, I5, F5:F15,”>=7/1/2021″, F5:F15, “<=7/31/2021”): The SUMIF function sums up all the values between our criteria. Here, the value is 10.
👉 SUMIFS(D5:D15, C5:C15, I6, F5:F15, “>=7/1/2021”, F5:F15, “<=7/31/2021”): The SUMIF function sums up all the values defined in our another criterion. Here, the value is 12.
👉 SUMIFS(D5:D15,C5:C15, I5, F5:F15,”>=7/1/2021″, F5:F15, “<=7/31/2021”) + SUMIFS(D5:D15, C5:C15, I6, F5:F15, “>=7/1/2021”, F5:F15, “<=7/31/2021”): Finally, the addition operator add both values and show it in cell I7. Here, the value is 22.
Read More: [Fixed]: SUMIFS Not Working with Multiple Criteria
5. Using Array Argument in SUM and SUMIFS Functions
Now, we will use the SUM and SUMIFS functions to apply an array argument and estimate the total; result. We are using our previous dataset for the array argument.
The steps to perform the array argument are given as follows:
📌 Steps:
- In the beginning, select cell I5.
- Afterward, write down the following formula inside the cell.
=SUM(SUMIFS(D5:D15,C5:C15, {"David","Wayne"}, F5:F15,">=7/1/2021", F5:F15, "<=7/31/2021"))
- Press Enter.
- You will notice that the formula will calculate your desired result.
In the end, we can say that our formula works properly, and we are able to use the SUMIFS function for the array argument.
🔎 Breakdown of the Formula
We are breaking down the formula for cell I5.
👉 SUMIFS(D5:D15,C5:C15, {“David”,”Wayne”}, F5:F15,”>=7/1/2021″, F5:F15, “<=7/31/2021”): The SUMIF function figure out the values validated between our criteria. Here, the value is 10, 12.
👉 SUM(SUMIFS(D5:D15,C5:C15, {“David”,”Wayne”}, F5:F15,”>=7/1/2021″, F5:F15, “<=7/31/2021”)): At last, the SUM function adds both values get by the SUMIF function. Here, the value is 22.
6. Combining SUMPRODUCT, ISNUMBER, and MATCH Functions for SUMIF Application
In the last example, we are going to use a combination of the SUMPRODUCT, ISNUMBER, and MATCH functions to work as the SUMIFS function. Our criteria are in the range of cells I4:I6. Here, we will determine the Total Price for our criteria.
The steps of this process are given as follows:
📌 Steps:
- At the start, select cell I7.
- After that, write down the following formula inside the cell.
=SUMPRODUCT(--(D5:D15>=I4), --(ISNUMBER(MATCH(C5:C15, I5:I6,0))), E5:E15)
- Then, press Enter.
- You will figure out your desired result.
Finally, we can say that our formula works successfully, and we are able to use the SUMIFS function for multiple criteria in different columns.
🔎 Breakdown of the Formula
We are breaking down the formula for cell I7.
👉 MATCH(C5:C15, I5:I6,0): The MATCH function will check the criteria and define them with 1 and 2. Here, we get three 1 for David and one 2 for Wayne.
👉 ISNUMBER(MATCH(C5:C15, I5:I6,0)): The ISNUMBER function checks the result of the MATCH function. If the result is numeric, it will show TURE. Otherwise, it will display FALSE.
👉 SUMPRODUCT(–(D5:D15>=I4), –(ISNUMBER(MATCH(C5:C15, I5:I6,0))), E5:E15): Finally, the SUMPRODUCT function sum the total price. Here, the value is $15,000.00.
Read More: SUMIFS with INDEX-MATCH Formula Including Multiple Criteria
Things to Remember About SUMIFS Function
- In Excel, the order of arguments of the SUMIF and SUMIFS functions are different. In particular, sum_range is the first (1st) parameter in SUMIFS, but it is the third (3rd) in SUMIF.
- In the SUMIFS function the sum range and criteria, of the range should be equally filled.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to use the SUMIFS function for multiple criteria in different columns in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations. Keep learning new methods and keep growing!
Related Articles
- How to Use SUMIFS with Multiple Criteria in the Same Column
- Exclude Multiple Criteria in Same Column with SUMIFS Function
- How to Use SUMIFS Function with Wildcard in Excel
- How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows
- How to Use SUMIFS Function with Multiple Sheets in Excel
- How to Use VBA Sumifs with Multiple Criteria in Same Column
