Sometimes we need to add values based on one or many criteria. Excel makes this easy by providing functions such as SUMIFS. Where SUMIF is for adding numbers based on one condition, SUMIFS can add numbers using multiple criteria or conditions. In this article, I will show different ways to use SUMIFS with multiple criteria in the same column.
What Is SUMIFS Function?
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)This is the syntax of the SUMIFS function. We can pass as many ranges and conditions as per our requirements. First, we need to pass a range of our sum, then the condition ranges where we will check our condition, and after that, we need to put our condition or criteria. In the same way, we can pass as much as we want.
SUMIFS with Multiple Criteria in Same Column: 5 Ways to Use
In this article, we will demonstrate to you how to use the SUMIFS function with multiple criteria in the same column in Excel by using the SUMIFS function.
1. Applying SUMIFS For OR Logic
We have a dataset of order details for any company. The table has four attributes, which are Oder ID, Product Names, Delivery Status, and Price. The delivery status could be Completed, Processing, Pending, or Delivered. Now I will show how to count the total prices where delivery status is Completed and Delivered.
Step 1:
- Firstly, choose the C15 cell.
- Secondly, type the following formula below.
=SUM(SUMIFS(E5:E12,D5:D12,{"Completed","Delivered"}))- Then, press ENTER.
Formula Explanation
- Before going to the main formula let’s see the syntax and fundamentals of the SUM function.
SUM(number1, [number2]….)
- This function takes any number of ranges to calculate the summation of them. We can pass one or many ranges of numbers like number1, number2… to get the total sum.
- The inner function is SUMIFS, using this function SUMIFS(E5:E12, D5:D12,{“Completed”, “Delivered”}) we find the rows where the delivery status is Completed or Delivered. After getting the matched rows summing up the prices and showing the result.
Step 2:
- Finally, the given image displays the total prices of completed and delivered products.
Read More: How to Apply SUMIFS with Multiple Criteria in Different Columns
2. Using SUMIFS For OR Logic with Wildcards
For this example, let’s assume we have a dataset of some fruits with their prices. Now I will show how to find the total prices of Lemons and Apples.
Step 1:
- Firstly, select the C16 cell.
- Secondly, type the following formula below.
=SUM(SUMIFS(C5:C13,B5:B13,{"*Apples","*Lemons"}))- Then, hit ENTER.
Formula Explanation
- This is the same formula that we used in the previous method. But here in the SUMIF function instead of giving the total string or text, I have used “*Apples” and “*Lemons” to find the fruit name which will be matched with the last name with this. Then all the prices will be summed up to get the total price using the SUM function.
Step 2:
- As a result, the given image shows the total prices of Lemons and Apples.
Read More: How to Use SUMIFS Function with Wildcard in Excel
3. Using SUMIFS with Dates
Let’s see how we can use the SUMIFS function with dates. For this example, let’s think we have a dataset of some fruits with their delivery date and quantity. Now I will illustrate how to find the number of quantities that were delivered in the last 120 days.
Step 1:
- Firstly, choose the C16 cell.
- Then, write down the following formula below here.
=SUMIFS(D5:D13, C5:C13,">="&TODAY()-120, C5:C13,"<="&TODAY())- Then, press ENTER.
Formula Explanation
- Here additionally I have used the TODAY function for counting today’s date.
TODAY()
- This is the syntax of the TODAY This function has no argument to pass in its parameter. This function is useful when you need to have the current date presented on a worksheet, although when you open the workbook.
- In the formula firstly I have passed the range of our cells which is D5:D13 then the condition range which is C5:C13. After that, we check if the criteria range is within the last 120 days from today or not. The quantities of the selected ranges will be summed up.
Step 2:
- Consequently, you will see the number of quantities delivered in the last 120 days.
Here is the result after passing the formula. You modify the days and check the results as per your requirements.
Read More: How to Use VBA Sumifs with Multiple Criteria in Same Column
Similar Readings
- How to Use SUMIFS When Cells Are Not Equal to Multiple Text
- Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria
- Exclude Multiple Criteria in Same Column with SUMIFS Function
4. Inserting SUMIFS with Multiple OR Criteria
Consider the same example above with another extra column which is Sellers. Now I will show how to get the total quantities for a specific month for all sellers. For this example, let’s consider the month is July.
Steps:
- Firstly, choose the C16 cell.
- After that, write down the following formula below.
=SUMIFS(E5:E13,C5:C13, H4, D5:D13,">=7/1/2021", D5:D13, "<=7/31/2021") + SUMIFS(E5:E13, C5:C13, H6, D5:D13, ">=7/1/2021", D5:D13, "<=7/31/2021") + SUMIFS(E5:E13, C5:C13, H7, D5:D13, ">=7/1/2021",D5:D13, "<=7/31/2021")- Then, press ENTER.
- Finally, you will see the total quantities for a specific month for all sellers in July.
Formula Explanation
- Here I have used three SUMIFS functions for each supplier. Firstly, the ranges which will be summed up are E5:E13 then the seller’s names range C5:C13. After that, the specific seller name is in H4, H6, and H7 Then we are comparing the delivery date ranges with our Delivery Dates column. As we are considering the July month for this example that’s why our date range is from 7/1/2021 to 7/31/2021.
5. Using SUMIFS to Sum Cells with Multiple OR Criteria in One Column
Let’s see how we can use the SUMIF function to sum cells with Multiple OR Criteria in One Column. For this let’s consider we have a dataset of fruits and quantities. Now I will show how to get the total quantities of the two specific fruits.
Step 1:
- Firstly, choose the F6 cell.
- After that, write down the following formula below.
=SUMIF(B5:B13,F4,C5:C13) + SUMIF(B5:B13,F5,C5:C13)- Then, press ENTER.
Formula Explanation
- Here I have used two SUMIF functionsThe first one SUMIF(B4:B12, F3, C4:C12) finds the rows of Lemons and sums the total quantities, and the second one SUMIF(B4:B12, F4, C4:C12) finds the rows of Apples and sums the total quantities.
Step 2:
- Lastly, you will see the total quantities of the two specific fruits.
Read More: SUMIFS with Multiple Criteria Along Column and Row in Excel
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice it by yourself.
Conclusion
In this article, we’ve covered 5 ways to use SUMIFS with multiple criteria in the same column in Excel. We sincerely hope you enjoyed and learned a lot from this article. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.
Related Articles
- Excel SUMIFS with Multiple Vertical and Horizontal Criteria
- Excel SUMIFS Not Equal to Multiple Criteria
- SUMIFS with INDEX-MATCH Formula Including Multiple Criteria
- How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows
- How to Use SUMIFS Function with Multiple Sheets in Excel
- [Fixed]: SUMIFS Not Working with Multiple Criteria
Hey Abdullah,
Helpful article; thanks for sharing. I am trying to sumifs using a date range, as you show above, but instead of having to type the starting date into the formula, I want to point at a cell that contains the date and then set rules off of that.
as an example, here is your formula above.
=SUMIFS(D4:D12, C4:C12,”>=”&TODAY()-60, C4:C12,”=”&Z13()-60, C4:C12,”<="&Z13())
Is something like that possible???
Hi, WILL!
Thank you for your query!
You can definitely point to a cell containing the desired date rather than typing it inside the formula. Say, you have placed the required date in the C15 cell.
In that case, you can use the following formula: