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

# How to Apply SUMIFS with Multiple Criteria in Different Columns

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.

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

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.

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

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

### 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 add 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.

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

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

Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!  