How to Use SUMIFS with Multiple Criteria in the Same Column

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 Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


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. For more information, you can visit this Link.


5 Ways to Use SUMIFS with Multiple Criteria in the Same Column

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 here.
=SUM(SUMIFS(E5:E12,D5:D12,{"Completed","Delivered"}))
  • Then, press ENTER.

Applying SUMIFS For OR Logic with Multiple Criteria in the Same Column

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. For more information, you can visit this Link.
  • The inner function is SUMIFS, using this function SUMIFS(E5:E12, D5:D12,{“Completed”, “Delivered”}) we are finding the rows where the delivery status is Completed or Delivered. After getting the matched rows are 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: SUMIFS Multiple Criteria Different Columns (6 Effective Ways)


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 here.
=SUM(SUMIFS(C5:C13,B5:B13,{"*Apples","*Lemons"}))
  • Then, hit ENTER.

Using SUMIFS For OR Logic with Wildcards with Multiple Criteria in the Same Column

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: SUMIFS with Wildcard in Excel (+ Alternative Formulas)


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.

Using SUMIFS with Dates with Multiple Criteria in the Same Column

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 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 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 result as per your requirements.

Read More: How to Use VBA Sumifs with Multiple Criteria in Same Column


Similar Readings

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 here.
=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.

Inserting SUMIFS with Multiple OR Criteria in the Same Column

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. 

Read More: How to Use SUMIFS with Date Range and Multiple Criteria (7 Quick Ways)


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 here.
=SUMIF(B5:B13,F4,C5:C13) + SUMIF(B5:B13,F5,C5:C13)
  • Then, press ENTER.

Using SUMIFS to Sum Cells with Multiple OR Criteria in One Column

Formula Explanation

  • Here I have used two SUMIF The 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: SUMIF with Multiple Criteria in Column & Row in Excel (Both OR and AND Type)


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. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Further Readings

Md. Abdullah Al Murad

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.

2 Comments
  1. 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:

      =SUMIFS(D4:D12, C4:C12,">="&$C$15-60, C4:C12,"<="&$C$15)

Leave a reply

ExcelDemy
Logo