Md. Abdullah Al Murad
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.
Download the Practice Workbook
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, after that we need to put our condition or criteria. In the same way, we can pass as much as we want. For more information, you can visit this Link
5 Ways to Use SUMIFS with Multiple Criteria in the Same Column
1. Using 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, Delivered. Now I will show how to count the total prices where delivery status is Completed and Delivered.
Step 1: Enter the formula in cell G4 and press Enter.
=SUM(SUMIFS(D4:D11,C4:C11,{"Completed","Delivered"}))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 numbers of range to calculate the summation of them. We can pass one or many ranges of numbers like number1, number2… etc to get the total sum. For more information, you can visit this Link
The inner function is SUMIFS, using this function SUMIFS(D4:D11, C4:C11,{“Completed”, “Delivered”}) we are finding the rows where delivery status is Completed or Delivered. After getting the matched rows are summing up the prices and showing the result.
[ Note you get the result for any other delivery status too by changing the status value in the main formula.]
2. Using SUMIFS using 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: Enter the formula in cell F4 and press Enter.
=SUM(SUMIFS(C4:C12,B4:B12,{"*Apples","*Lemons"}))Formula Explanation
This is the same formula that we have 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.
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 60 days.
[ Note: It will count the last 60 days from the current date. Today my date is 11-Jul-2021 so here all the calculations will be based on my current date. Your total quantity will differ if you download the workbook the day after today.]
Step 1: Enter the formula in cell G4 and press Enter.
=SUMIFS(D4:D12, C4:C12,">="&TODAY()-60, C4:C12,"<="&TODAY())Formula Explanation
Here additionally I have used the TODAY function for counting today’s date.
TODAY()
This is the syntax of the TODAY function. 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 of when you open the workbook. For more details, you can visit the Link
In the formula firstly I have passed the range of our cells which is D4:D12 then the condition range that is C4:C12. After that, we check if the criteria range is within the last 60 days from today or not. The quantities of the selected ranges will be summed up.
Here is the result after passing the formula. You modify the days and check the result as per your requirements.
4. 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.
Step 1: Enter the formula in cell H7 and press Enter.
=SUMIFS(E4:E12,C4:C12, H3, D4:D12,">=7/1/2021", D4:D12, "<=7/31/2021") + SUMIFS(E4:E12, C4:C12, H5, D4:D12, ">=7/1/2021", D4:D12, "<=7/31/2021") + SUMIFS(E4:E12, C4:C12, H6, D4:D12, ">=7/1/2021",D4:D12, "<=7/31/2021")Here I have used three SUMIFS functions for each supplier. Firstly, the ranges which will be summed up are E4:E12 then the seller’s names range C4:C12. After that, the specific seller name is in H3, H5, and H6 cells. 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.
Step 2: All the counted results will be showed.
5. Alternative Ways
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: Enter the formula in cell F5 and press Enter.
=SUMIF(B4:B12,F3,C4:C12) + SUMIF(B4:B12,F4,C4:C12)Formula Explanation
Here I have used two SUMIF functions. The first one SUMIF(B4:B12, F3, C4:C12) finds the rows of Lemons and sum the total quantities, the second one SUMIF(B4:B12, F4, C4:C12) finds the rows of Apples and sum the total quantities.
Conclusion
These are the ways to use SUMIFS functions with multiple criteria in the same column in Excel. I have shown all the methods with their respective examples but there can be many other iterations. Also, I have discussed the fundamentals of the used functions. If you have any other method of achieving this then please feel free to share it with us.
