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.

**Table of Contents**hide

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

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

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

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

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

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

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

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

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

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

**Things to Remember**

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.**Size of Sum range and Criteria range:****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
**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.