Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel SUMIF with Multiple Criteria (5 Handy Examples)

For conditional sum in Excel, SUMIF is one of the most used functions provided by MS Excel. It is a combination of SUM and IF (SUM+IF), which means it checks a condition and returns a corresponding summation value. In this article, I will explain how to use the SUMIF function with multiple criteria.


Download Practice Workbook

You can download the following workbook to practice while reading this article.


Introduction to SUMIF Function in Excel

SUMIF function in its arguments is given one criterion. It first checks whether the condition is met or not. Then it sums up the corresponding values of the specified range.

Let’s see the syntax of this function.

SUMIF(range, criteria, [sum_range])

In this function, three arguments can be passed. Among them, the first two are required and one is optional.

  • Range- This portion takes the ranges of cells that we want to be estimated by criteria. Cells in each range can be in numbers or names, arrays, or references that will contain numbers.
  • criteria- This argument holds the criteria or condition based on which our sum will be calculated.
  • [sum_range]- The last argument which is optional. The exact cells to add if we want to add cells other than those defined in the range argument.

Can We Do SUMIF in Excel with Multiple Criteria?

The SUMIF function in Excel is not dedicated to matching multiple criteria. So, if you want to use this function with multiple criteria, you have to play some tricks. In the following examples, we will see some of them.


5 Examples of Using SUMIF Function in Excel with Multiple Criteria

Let’s say that we have a dataset of some fruits with their sellers, delivery dates, and quantities. We will use the following data in all these examples.

Sample Data to Apply SUMIF with Multiple Criteria


1. Using SUMIF Function Multiple Times in a Formula

As we can see in one SUMIF function we can pass only criteria or conditions, that’s why for multiple conditions we need multiple SUMIF and this will work like OR logic.

Now using the SUMIF function we will count the total quantities of fruits provided by any three specific sellers.

Steps:

  • First, enter any three sellers’ names in the input cells H5:H7.
  • Then, enter the following formula in cell H8 and press Enter.
=SUMIF(C5:C13,H5,E5:E13)+SUMIF(C5:C13,H6,E5:E13)+SUMIF(C5:C13,H7,E5:E13)

Using SUMIF Function Multiple Times in a Formula

Formula Explanation:

Here I have used three SUMIF functions.

  • SUMIF(C5:C13,H5,E5:E13) is calculating total quantities for David,
  • SUMIF(C5:C13,H6,E5:E13) is for Jacob,
  • And lastly, SUMIF(C5:C13,H7,E5:E13) is for Charlie.

Related Content: Sum Multiple Columns Based on Multiple Criteria in Excel


2. Combining SUM and SUMIF Functions with an Array Argument

In this method, we will use an array as an argument to pass multiple criteria. For this let’s assume the same example above but here we will use a different formula. Additionally, here we will need the SUM function.

Steps:

  • First, insert the following formula in cell H8.
=SUM(SUMIF(C5:C13,{"David","Jacob","Charlie"},E5:E13))

Combining SUM and SUMIF Functions with an Array Argument

  • Then press Enter.

Formula Explanation:

  • SUMIF(C4:C12, {“David”,”Jacob”,”Charlie”}, E4:E12).

In this function, we are passing an array as criteria which is {“David”,”Jacob”,”Charlie”}. This SUMIF function checks the condition using this array and checks to find names same as this array elements.

  • Lastly, the SUM calculates the total of each seller’s quantities.

Note:

You cannot use any cell reference in the formula. As we are using array as criteria we need to pass each seller’s name in the formula manually.

Read More: SUMIF Multiple Ranges [6 Useful Ways]


3. Combining SUMPRODUCT with SUMIF to Match Multiple Criteria

Here we will see how to use SUMIF with multiple criteria using another additional function which is SUMPRODUCT. For this method, we will consider the exact same example which was used in the previous methods.

Steps:

  • Write the following formula in cell H8.
=SUMPRODUCT(SUMIF(C5:C13,H5:H7,E5:E13))
  • And press Enter.

Combining SUMPRODUCT with SUMIF to Match Multiple Criteria

Formula Explanation:

  • SUMIF(C4:C12, H4:H6, E4:E12)

This part of the formula extracts the cells which match with our input data those are in H4:H6 cells.

  • After finding them, the SUMPRODUCT function calculates the total sum of the matched cells.

Read More: SUMIF with Multiple Criteria for Different Columns in Excel


4. Using Asterisk Operator with SUMIF to Match Multiple Items

A bit different technique is this, where you will match fruit names partially with a single SUMIF function and get the regarding sum. Let me be more clear. Apple and Lemon- both have “le” parts. If you utilize the asterisk (*) operator (a wildcard character) in the SUMIF function, you can get the sum for both of these fruits.

Steps:

  • For that, enter the following formula in cell H8:

=SUMIF(B5:B13,"*le*",E5:E13)

  • Then press Enter.

Using Asterisk Operator with SUMIF to Match Multiple Items


5. SUMIF with Multiple Ranges and Criteria with a Helper Column

To use SUMIF for multiple ranges, we need to add a helper column. We will add the values from multiple ranges using the SUM function in the helper column. We will use the following dataset in this example.

Steps:

  • First of all, add a helper column in column H.
  • Now, go to cell H6 and write the following formula.
=SUM(D6:G6)
  • Then drag the fill handle icon to fill the rest of the cells.

  • Now, in cell K7, insert the following formula:
=SUMIF(B6:B17,$J$7,H6:H17)

SUMIF with Multiple Ranges and Criteria with a Helper Column

This formula returns the total price of Apples purchased in January, February, and March on different dates.


SUMIFS with SUM Function: An Alternative Approach to Match Multiple Criteria in Excel

Up to now, we were using SUMIF for multiple criteria. Now we will see the best function which can be used for summation with multiple criteria which is SUMIFS.

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 showing this process we will use the same dataset overhead.

Steps:

  • Enter the following formula in cell H8.
=SUM(SUMIFS(E5:E13,C5:C13,H5:H7))
  • And press Enter.

Formula Explanation:

  • SUMIFS(E5:E13,C5:C13,H5:H7)

The SUMIFS matches the names in H5:H7 with C5:C13 and returns the regarding values from the E5:E13 range. The result is: {82;10;476}

  • SUM({82;10;476})

The SUM function then sums up these values. The result is: 568


Things to Remember

  • Size of Sum range and Criteria Range:
    In this function, the sum_range argument does not certainly have to be of the equal size as the range argument, as long as you have the top-left cell right.
  • The Sequence of Arguments:
    You need to maintain the order of arguments in the SUMIF function. Excel assists the user by providing the argument’s name while typing so you check the arguments at the time using.
  • When we use multiple criteria with the SUMIFS function then it works like the logical OR function.

Conclusion

These are the ways to use SUMIF functions with multiple criteria 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. To know more about MS Excel, visit our blog.


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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo