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.

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.

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.

• 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: How to Use SUMIFS with Multiple Criteria in the Same Column

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.

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

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

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

Read More: Excel SUMIFS with Multiple Vertical and Horizontal 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.

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

Read More: SUMIFS: Sum Range Across Multiple Columns

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.

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

Read More: How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows

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

• SUMIFS with Multiple Criteria Along Column and Row in Excel
• Exclude Multiple Criteria in Same Column with SUMIFS Function
• How to Use VBA SUMIFS with Multiple Criteria in Same Column

<< Go Back to Excel SUMIFS with Multiple Criteria | Excel SUMIFS Function | Excel Functions | Learn Excel