SUMIF with Multiple Criteria (5 Easiest Examples)

Enter formula using multiple SUMIF and press Enter

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 conditions based on conditions and returns a summation value. In this article, I will explain how to use SUMIF function with multiple criteria.

What is the SUMIF Function?

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.

Read more: SUMIF Multiple Ranges

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.

For more information, you can visit this Link

5 Ways to Use SUMIF with Multiple Criteria

1. Using Multiple SUMIF Functions 

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.

Let’s say that we have a dataset of some fruits with their sellers, delivery date, and quantities. Now using the SUMIF function we will count the total quantities of fruits provided by any three specific sellers.

Using Multiple SUMIF Functions

Step 1: Enter any fruit three sellers’ names in the input sales.

Enter any three sellers name

Step 2: Enter the formula in cell H7 and press Enter.

=SUMIF(C4:C12,H4,E4:E12) + SUMIF(C4:C12,H5,E4:E12)+SUMIF(C4:C12,H6,E4:E12)

Formula Explanation

Here I have used three SUMIF functions. SUMIF(C4:C12,H4,E4:E12) is calculating total quantities for David, SUMIF(C4:C12,H5,E4:E12) is for Jacob and lastly, SUMIF(C4:C12,H6,E4:E12) is for Charlie.

 Enter formula using multiple SUMIF and press Enter

Step 3: You can check by giving any invalid input (seller’s name that is not in the table).

Checking out by giving invalid names

[ As the giving names are not in the list that’s why the total quantities are showing 0.]

2. Using SUM & SUMIF 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.

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

Step 1: Enter the formula in cell H7 and press Enter.

=SUM(SUMIF(C4:C12, {"David","Jacob","Charlie"}, E4:E12))

Formula Explanation

First see the inner function which is 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 checking the condition using this array and check to find names same as this array elements. Lastly, the SUM calculates the total of each seller’s quantities.

 Enter formula using SUM and SUMIF

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

3. Using SUMPRODUCT & SUMIF Functions

Here we will see how to use SUMIF with multiple criteria using another additional function which is SUMPRODUCT.

SUMPRODUCT(array1, [array2], [array3], …)

It takes the array in its parameter and returns the sum. In this function, we can pass multiple arrays to get the total sum product. For more details, you can visit this Link

For this method, we will consider the exact same example which was used in the previous methods.

Step 1: Enter the formula in cell H7 and press Enter.

=SUMPRODUCT(SUMIF(C4:C12, H4:H6, E4:E12))

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 SUMPRODUCT function calculates the total sum of the matched cells.

Enter formula using SUMPRODUCT and SUMIF

Step 2: You can check by giving any input in H4 to H7 cells.

Check output by changing input

4. Using SUMIF based on Partial Match

Now let’s see how to calculate sum with multiple criteria by checking partial match in the input data. For this, we will consider a dataset of Fruits like the previous one. But now there will be a little bit of change in the dataset. Our task will be to find the no of fruits which is named Apples and Lemons.

Using SUMIF based on Partial Match

Formula Explanation

Here I have used two SUMIF functions. SUMIF(B4:B12,”*Apples*”,E4:E12) this one searches for Apples and any names partially named as Apples. SUMIF(B4:B12,”*Lemons*”,E4:E12)

Does the same thing for Lemons.

Enter the formula in cell G5

5. Alternative Approach (Best Way)

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, 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

For showing this process we will use the same dataset overhead.

Step 1: Enter the formula in cell H8 and press Enter.

=SUM(SUMIFS(E5:E13,C5:C13,{"David","Jacob","Charlie"}))

Formula Explanation

Here using SUMIFS instead of SUMIF. SUMIFS(E5:E13,C5:C13,{“David”,”Jacob”,”Charlie”}) This portion finds the cells where seller names are as our given input and the SUM function returns the total summation.

Enter formula using SUM and SUMIFS

Things to Remember

  1. 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.
  2. 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.
  3. When we use multiple criteria with the SUMIFS function then it works like the OR logical 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.


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