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, we will show you 6 effective ways to use **SUMIFS **for multiple criteria in different columns. If you are also curious to know, download our practice workbook and follow us.

## Use SUMIFS with Multiple Criteria in Different Columns: 6 Suitable Examples

To demonstrate the approaches, we consider a dataset of 11 items, and their vendors’ names, quantities, and prices are mentioned here. So, our dataset is in the range of cells **B5:E15**.

**📚 Note:**

All the operations of this article are accomplished by using the **Microsoft Office 365** application.

### 1. Apply SUMIFS Function with Comparison Operators

In the first example, we are going to compare with a given data and sum all the values greater than that value using **the SUMIFS function**. Our criteria value is in cell **H4**, and the comparison criteria are in cell **H5**.

The steps to finish this example are given below:

**📌 Steps:**

- First of all, select cell
**H6**. - Now, write down the following formula in the cell.

`=SUMIFS(D5:D15,C5:C15,H4,D5:D15,H5)`

- Press
**Enter**.

- You will get the sum result.

Thus, we can say that our formula works perfectly, and we are able to use the **SUMIFS** function for multiple criteria for different columns.

**Read More:** **Excel SUMIFS Not Equal to Multiple Criteria**

### 2. Using SUMIFS Function with Date Value

In the second example, we will use the **SUMIFS** and **TODAY** functions to add all the values based on our desired date. For that, we will add a new column in column **F**, entitled **Delivery Date**. Our vendor criteria value will be in cell **I4**. The steps to complete this example are given as follows:

**📌 Steps:**

- First, select cell
**I5**. - After that, write down the following formula in the cell.

`=SUMIFS(D5:D15, F5:F15,">="&TODAY()-20, F5:F15,"<="&TODAY(),C5:C15,I4)`

- Now, press
**Enter**.

- It will show you
**zero (0)**in cell**I5**. - Then, write down the vendor criteria in cell
**I4**. Here, we wrote**David**.

- You will notice that the formula sums up the quality of the product which lies within our time limit.

Hence, we can say that our formula works effectively, and we are able to use the **SUMIFS** function for multiple criteria for different columns.

### 3. Applying SUMIFS Function with Blank Cells

In the following example, the** SUMIFS **function will help us to sum values based on the blank cell remaining in the dataset. We have **4** entities for vendor **David**. Among them, **2** delivery dates are blank cells.

The steps to accomplish this example are shown below:

**📌 Steps:**

- At first, select cell
**I5**. - Then, write down the following formula inside the cell.

`=SUMIFS(D5:D15, F5:F15,"=",C5:C15,I4)`

- Afterward, press
**Enter**, and you will get a**0**value in that cell.

- Now, write down
**David**as the vendor criteria in cell**I4**. - You will see that the formula will sum up the quality of the product for the blank cells, and the other two cells will be omitted.

Therefore, we can say that our formula works precisely, and we are able to use the **SUMIFS** function for multiple criteria for different columns.

**Read More:** **Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria**

**Similar Readings**

**Excel SUMIFS with Multiple Vertical and Horizontal Criteria****How to Use SUMIFS When Cells Are Not Equal to Multiple Text****SUMIFS with Multiple Criteria Along Column and Row in Excel**

### 4. Utilizing SUMIFS Function with Multiple OR Criteria

Here, we are going to use the** SUMIFS **function to perform the **OR** operation with multiple criteria. Our desired criteria are in the range of cells **I4:I6**.

The steps to perform the operation are described below:

**📌 Steps:**

- Firstly, select cell
**I7**. - Next, write down the following formula inside the cell.

`=SUMIFS(D5:D15,C5:C15, I5, F5:F15,">=7/1/2021", F5:F15, "<=7/31/2021") + SUMIFS(D5:D15, C5:C15, I6, F5:F15, ">=7/1/2021", F5:F15, "<=7/31/2021")`

- Now, press
**Enter**.

- You will get your desired result.

So, we can say that our formula works fruitfully, and we are able to use the **SUMIFS** function to perform multiple **OR** operations from multiple criteria.

**🔎 Breakdown of the Formula**

We are breaking down the formula for cell **I7**.

`👉`

**SUMIFS(D5:D15,C5:C15, I5, F5:F15,”>=7/1/2021″, F5:F15, “<=7/31/2021”)**: The **SUMIF** function sums up all the values between our criteria. Here, the value is **10**.

`👉`

**SUMIFS(D5:D15, C5:C15, I6, F5:F15, “>=7/1/2021”, F5:F15, “<=7/31/2021”)**: The **SUMIF** function sums up all the values defined in our another criterion. Here, the value is **12**.

`👉`

**SUMIFS(D5:D15,C5:C15, I5, F5:F15,”>=7/1/2021″, F5:F15, “<=7/31/2021”) + SUMIFS(D5:D15, C5:C15, I6, F5:F15, “>=7/1/2021”, F5:F15, “<=7/31/2021”)**: Finally, the addition operator add both values and show it in cell **I7**. Here, the value is **22**.

**Read More:** **[Fixed]: SUMIFS Not Working with Multiple Criteria**

### 5. Using Array Argument in SUM and SUMIFS Functions

Now, we will use the **SUM** and **SUMIFS** functions to apply an array argument and estimate the total; result. We are using our previous dataset for the array argument.

The steps to perform the array argument are given as follows:

**📌 Steps:**

- In the beginning, select cell
**I5**. - Afterward, write down the following formula inside the cell.

`=SUM(SUMIFS(D5:D15,C5:C15, {"David","Wayne"}, F5:F15,">=7/1/2021", F5:F15, "<=7/31/2021"))`

- Press
**Enter**.

- You will notice that the formula will calculate your desired result.

In the end, we can say that our formula works properly, and we are able to use the **SUMIFS** function for the array argument.

**🔎 Breakdown of the Formula**

We are breaking down the formula for cell **I5**.

`👉`

**SUMIFS(D5:D15,C5:C15, {“David”,”Wayne”}, F5:F15,”>=7/1/2021″, F5:F15, “<=7/31/2021”)**: The **SUMIF** function figure out the values validated between our criteria. Here, the value is **10**, **12**.

`👉`

**SUM(SUMIFS(D5:D15,C5:C15, {“David”,”Wayne”}, F5:F15,”>=7/1/2021″, F5:F15, “<=7/31/2021”))**: At last, the **SUM** function adds both values get by the **SUMIF** function. Here, the value is **22**.

### 6. Combining SUMPRODUCT, ISNUMBER, and MATCH Functions for SUMIF Application

In the last example, we are going to use a combination of the **SUMPRODUCT**, **ISNUMBER**, and **MATCH** functions to work as the **SUMIFS** function. Our criteria are in the range of cells **I4:I6**. Here, we will determine the Total Price for our criteria.

The steps of this process are given as follows:

**📌 Steps:**

- At the start, select cell
**I7**. - After that, write down the following formula inside the cell.

`=SUMPRODUCT(--(D5:D15>=I4), --(ISNUMBER(MATCH(C5:C15, I5:I6,0))), E5:E15)`

- Then, press
**Enter**.

- You will figure out your desired result.

Finally, we can say that our formula works successfully, and we are able to use the **SUMIFS** function for multiple criteria in different columns.

**🔎 Breakdown of the Formula**

We are breaking down the formula for cell** I7**.

`👉`

**MATCH(C5:C15, I5:I6,0)**: The **MATCH** function will check the criteria and define them with **1** and **2**. Here, we get **three** **1** for **David** and one **2** for **Wayne**.

`👉`

**ISNUMBER(MATCH(C5:C15, I5:I6,0))**: The **ISNUMBER** function checks the result of the **MATCH** function. If the result is numeric, it will show **TURE**. Otherwise, it will display **FALSE**.

`👉`

**SUMPRODUCT(–(D5:D15>=I4), –(ISNUMBER(MATCH(C5:C15, I5:I6,0))), E5:E15)**: Finally, the **SUMPRODUCT** function sum the total price. Here, the value is **$15,000.00**.

**Read More:** **SUMIFS with INDEX-MATCH Formula Including Multiple Criteria**

**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, of the range should be equally filled.

**Download Practice Workbook**

Download this practice workbook for practice while you are reading this article.

**Conclusion**

That’s the end of this article. I hope that this article will be helpful for you and you will be able to use the **SUMIFS** function for multiple criteria in different columns in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations. Keep learning new methods and keep growing!

## Related Articles

**How to Use SUMIFS with Multiple Criteria in the Same Column****Exclude Multiple Criteria in Same Column with SUMIFS Function****How to Use SUMIFS Function with Wildcard in Excel****How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows****How to Use SUMIFS Function with Multiple Sheets in Excel****How to Use VBA Sumifs with Multiple Criteria in Same Column**