How to Apply SUMIFS with Multiple Criteria in Different Columns

Get FREE Advanced Excel Exercises with Solutions!

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.

Apply SUMIFS Function with Comparison Operators

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

Using SUMIFS Function with Date Value

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

Insert the criteria to use the SUMIFS function for multiple criteria in different columns

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

Applying SUMIFS Function with Blank Cells for Multiple Criteria

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

Input David as a criteria to use SUMIF function for multiple criteria in different columns

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


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.

Utilizing SUMIFS Function with Multiple OR Criteria in Different Columns

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

Using Array Argument in SUM and SUMIFS Functions

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

Combining SUMPRODUCT, ISNUMBER, and MATCH Functions for SUMIF Application for Multiple Criteria in Different Columns

  • 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

Md. Abdullah Al Murad
Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo