SUMIFS with Multiple Criteria in the Same Column (5 Ways)

Result for OR using SUMIFS

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.

Using SUMIFS for OR Logic

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.

Result for OR using SUMIFS

[ 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.

Using SUMIFS for OR logic with wildcards

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.

 Enter the formula using SUMIF with wildcards

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.] 

Using SUMIFS with dates

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.

Sumif function with date formula

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.

SUMIFS with multiple OR

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")
Formula Explanation

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. 

Enter the formula with multiple SUMIFS

Step 2: All the counted results will be showed.

Result after using Multiple SUMIFS

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.

Sum cells with multiple or using SUMIF

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.

Formual using multiple sumif

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.


Further Readings

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo