Sometimes we may need to get a summation based on different criteria. For this purpose, Excel provides a function called **SUMIFS**. This is the updated version of the **SUMIF **function. In this article, I will show how to use **SUMIFS **for multiple criteria in different columns.

**Table of Contents**hide

## Download the Practice WorkBook

**What is the SUMIFS Function?**

The syntax of this function is like this:

`SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)`

Let’s see the details of every argument.

**Sum_range -> **This portion contains the range of cells to sum.

**Criteria_range1 -> **Here we need to pass the range that is tested using the first criteria.

**Criteria1 -> **This is our first criteria or condition.

**[criteria_range2, criteria2], … -> **This means we can pass the criteria range and criteria as much as we want.

**6 Ways to Use SUMIFS With Multiple Criteria in Different Columns**

### 1. SUMIFS with Comparison Operators

As I have mentioned the **SUMIFS **function uses logical conditions that’s why we can use it for different comparison purposes. For this, **SUMIFS **allows using a comparison operator (<, =, >) to do a comparison. Let’s assume we have a dataset of office setup. In our dataset Product Name, Vendor, Quantity, and Price. Our task is to find the total quantity by specifying the vendor name and a quantity ( the selected quantity should be equal to or above this quantity)

**Step 1:** Enter the formula in cell **H5**

`=SUMIFS(D4:D14,C4:C14,H3,D4:D14,H4)`

**Formula Explanation**

**D4:D14**This is the**Quantity**range that will be summed up.**C4:C14**this range contains the**Vendor’s**list which will be matched with input which is in the**H4**cell.**D4:D14**this range contains the**Quantity**range which will be matched with input field**H5**.

**Step 2: **Enter the Vendor name and quantity then press **Enter**

*[ Note: As we want to total quantity which is equal or greater than input quantity that’s why we need to put >= sign before the quantity number.]*

**2. SUMIFS with Dates**

Here we will see how to deal with the SUMIFS function with dates. For this, we will consider the same dataset above with an extra column named Delivery Date. Now, our task is to find the total quantity which was delivered by any specific vendor in the last 20 days from today. I like to mention that for me today is 19th July 2021. Our all calculations will be calculated based on this date.

**Step 1:** Enter the formula in cell **I4**

`=SUMIFS(D4:D14, F4:F14,">="&TODAY()-20, F4:F14,"<="&TODAY(),C4:C14,I3)`

**Formula Explanation**

**D4:D14**this is the range where we will calculate the total summation.**F4:F14**this is the criteria range and**“>=”&TODAY()-20**this is the criteria for the first range. As we want to calculate the last 20 dates that’s why we are using the**TODAY**function and**TODAY()-20**gives the last 20 dates from today.**F4:F14,”<=”&TODAY()**this the highest range of dates. It will calculate the dates up to today.**C4:C14, I3**Here we are matching the vendor name with our vendor list.

**Step 2: **Enter any vendor name and press **Enter**

**3. SUMIFS with Blank Cells**

Now let’s see how to count blank cells using the **SUMIFS **function. For this method, we just need to modify the previous dataset. Here we will find out the total quantity for any specific vendor which has not been delivered yet. Now how can we measure that the product is delivered or not? Well, we just simply do not put any data in data in the Delivery date column.

**Step 1:** Enter the formula in cell **I4**

`=SUMIFS(D4:D14, F4:F14,"=",C4:C14,I3)`

**Formula Explanation**

**D4:D14**this is the sum range.**F4:F14,”=”**this portion checks the blank ranges in the delivery date column.**C4:C14, I3**it finds the matched rows with our input.

**Step 2: **Enter any vendor name and press **Enter**

**4. SUMIFS with Multiple OR Criteria**

Here we will see how we can use the **SUMIFS **function where it will behave like OR logic. OR logic means if any of the conditions are met then it will be counted as true. Again, our dataset will be the same as above. Our target is to find the supplied product quantity for any specific two vendors in a specific month.

**Step 1: **Enter the formula in the **I6 **cell

`=SUMIFS(D4:D14,C4:C14, I4, F4:F14,">=7/1/2021", F4:F14, "<=7/31/2021") + SUMIFS(D4:D14, C4:C14, I5, F4:F14, ">=7/1/2021", F4:F14, "<=7/31/2021")`

**Formula Explanation**

Here we have used two SUMIFS functions. Two functions work the same way for different vendors. Let’s see the details of one of them.

**SUMIFS(D4:D14,C4:C14, I4, F4:F14,”>=7/1/2021″, F4:F14, “<=7/31/2021”)**this is for vendor 1.**D4:D14**is the range where we will calculate the sum.**C4:C14, I4**is for matching the 1st vendor name from the vendor column.**F4:F14,”>=7/1/2021″**and**F4:F14, “<=7/31/2021”**these two portions define the range of July month.

**Step 2: **Enter any two vendors’ names and press **Enter**

### 5. **Using Array Argument in SUM & SUMIFS**

We can simplify the formula which was used in the previous method (method 4) by using an array argument where the functions will be **SUM **and **SUMIFS**

**Step 1: **Enter the formula in **I6**

`=SUM(SUMIFS(D4:D14,C4:C14, {"David","Wayne"}, F4:F14,">=7/1/2021", F4:F14, "<=7/31/2021"))`

**Formula Explanation**

- Inside the SUMIFS function here we have used an array argument as criteria which is
**{“David”, “Wayne”}**this will find the elements which are in the array from**C4**to**C14**range. - Lastly, the
**SUM**function will calculate the total selected quantities and show them.

### 6. **Alternative of SUMIFS Function**

**Using SUMPRODUCT Function**

We can do the same operation of **SUMIFS **with another Excel function which is **SUMPRODUCT**. Again, for this, we will use the same dataset that we were using. Our target is to find the total price equal to or above any specific quantity for two vendors.

**Step 1: **Enter the formula in cell **I6**

`=SUMPRODUCT(--(D4:D14>=I3), --(ISNUMBER(MATCH(C4:C14, I4:I5,0))), E4:E14)`

**Formula Explanation**

**–(D4:D14>=I3)**this part finds the quantities greater or equal to the input.**–(ISNUMBER(MATCH(C4:C14, I4:I5,0)))**this part matched the vendor’s name.**E4:E14**this is the range that will be summed up.

**Step 2: **Enter the quantity and two vendors names and check the output

**Things to Remember About SUMIFS Function**

- In Excel, the order of arguments of the SUMIF and SUMIFS functions are different. In particular, sum_range is the first (1st) parameter in
**SUMIFS**, but it is the third (3rd) in**SUMIF**. - In the
**SUMIFS**function the sum range and criteria, the range should be equally filled.

**Conclusion**

These are the ways to use **SUMIFS **for multiple criteria with multiple columns. 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.