Shakil Ahmed
We often need to sum data that fulfill particular criteria. The summation may include fulfilling multiple criteria in different columns. Excel provides the SUMIF function to sum data with criteria. In this article, we will show how to apply SUMIF with multiple criteria in different columns in Excel.
Download Practice Workbook
You can download the practice workbook here.
3 Easy Ways to Apply SUMIF with Multiple Criteria in Different Columns in Excel
In this article, we will discuss 3 handy ways to apply SUMIF with multiple criteria in different columns in Excel. We will use the SUMIF function with single criteria in the first method. Then, we will use the SUMIF function for multiple criteria. This method will have sub-methods where we will use OR logic, array formulas, and the SUMPRODUCT function to sum with multiple criteria. Finally, we will opt for the SUMIFS function with multiple criteria. Here is a sample dataset that we will use to demonstrate the methods.
1. Using SUMIF Function for Single Criteria
The SUMIF function adds data that fulfill particular criteria. In this method, we will use this function with single criteria.
Steps:
- Firstly, select the I4 cell and type,
=SUMIF($B$5:$B$16,H4,E5:E16)- Then, hit Enter.
- Consequently, we will have the summation of revenues on ‘Shirt’.
- Repeat the process for ‘Pant’ and ‘T-Shir’ as well.
Formula Breakdown:
- SUMIF($B$5:$B$16,H4,E5:E16): The formula will go through the B5:B16 range to look for the value in the H4 cell which is Shirt, and then sum all the values in the range E5:E16 which are associated with the value Shirt and return the sum.
Read More: How to Sum Multiple Columns Based on Multiple Criteria in Excel
2. Using SUMIF for Multiple Criteria
In this method, we will go through multiple criteria and then sum the data that fulfill those criteria using the SUMIF function. We will see through some sub-methods to demonstrate the process.
2.1. Applying OR Logic
Usually, the SUMIF function takes a single criterion into the account. In this sub-method, we will use the SUMIF function with OR logic. We will add multiple SUMIF functions to fulfill multiple criteria and each value from each SUMIF function will be added to fulfill multiple criteria.
Steps:
- Firstly, select the J5 cell and enter the following formula,
=SUMIF($B$5:$B$16,H5,$E$5:$E$16)+SUMIF($B$5:$B$16,I5,$E$5:$E$16)=SUMIF($B$5:$B$16,H4,E5:E16)- Then, press Enter.
- Consequently, we will sum up the values fulfilling multiple criteria.
Formula Breakdown:
- SUMIF($B$5:$B$16,H5,$E$5:$E$16): The SUMIF function will return the total sum of the revenues that are associated with the value in the H5 cell which is Shirt.
- SUMIF($B$5:$B$16,I5,$E$5:$E$16): This will return the sum of the values associated with the value ‘Pants’ in the E5:E16 range.
- SUMIF($B$5:$B$16,H5,$E$5:$E$16)+SUMIF($B$5:$B$16,I5,$E$5:$E$16): This expression sums the values returned by the previous two expressions.
2.2. Using Array within SUM Function
In this method, we will use an array within the SUMIF function as the criteria to sum the values in the data. This will not only shorten the formula but also make it more readable.
Steps:
- To begin with, choose the J5 cell and enter the following formula,
=SUM(SUMIF($B$5:$B$16,{"Shirt","Pants"},F5:F16))- Hit Enter.
- As a result, we will get the summation of profits from the products ‘Shirt’ and ‘Pants’.
Formula Breakdown:
- SUMIF($B$5:$B$16,{“Shirt”,”Pants”},F5:F16): Here the SUMIF function will scan through the range B5:B16 to look for Shirt and Pants and then sum up the profits in the F5:F16 range for those two products and return them as the input of the SUM function.
- SUM(SUMIF($B$5:$B$16,{“Shirt”,”Pants”},F5:F16)): Finally, the SUM function will return the sum of the profit from those two products.
2.3. Applying Array Formula
In this method, we will insert a range as criteria instead of inserting values. This is called an array formula. The SUMIF function will evaluate the range as criteria and return the sum of all the values associated with that criterion in that range.
Steps:
- Firstly, select the J5 cell and insert the following formula,
=SUM(SUMIF(B5:B16,H5:I5,F5:F16))- Then, hit the Enter button.
- As a result, we will get the total profit from Shirt and Pants which were our intended criteria range.
Formula Breakdown:
- SUMIF(B5:B16,H5:I5,F5:F16): We will insert the values in the H5:I5 range as our criteria. Then, the SUMIF function will go through the B5:B16 range to look for the criteria values and sum the values associated with those criteria values individually. This means it will sum all the profits from Shirt and all the profit from the Pants and return them as arguments for the SUM function.
- SUM(SUMIF(B5:B16,H5:I5,F5:F16)): Finally, the SUM function will sum the values returned by the SUMIF function for the two criteria values.
2.4. Using Array with SUMPRODUCT Function
In this method, we will do the same as the previous method except for the fact that here, we will use the SUMPRODUCT function instead of the SUM function.
Steps:
- To start with, choose the J5 cell and enter the following formula,
=SUMPRODUCT(SUMIF($B$5:$B$16,H5:I5,$F$5:$F$16))- Press Enter.
- As a result, we will get the total profit from the criteria mentioned in the criteria range.
Read More: SUMIF for Multiple Criteria Across Different Sheets in Excel (3 Methods)
3. Using SUMIFS for Multiple Criteria
The SUMIFS function is Excel’s default function for summing up values with multiple criteria. It takes in multiple values as criteria and also their ranges as arguments. Finally, sums the values returned with accordance to the criteria.
Steps:
- Firstly, choose the J5 cell and enter the following formula,
=SUMIFS($E$5:$E$16,$B$5:$B$16,H5,$C$5:$C$16,I5)- Then, press the Enter button.
- As a result, we will get the total revenue from shirts with white color which were our two criteria.
Formula Breakdown:
- SUMIFS($E$5:$E$16,$B$5:$B$16,H5,$C$5:$C$16,I5): The first argument, $E$5:$E$16, is the sum range of the function. In this case, the range denotes revenue. The second argument, $B$5:$B$16, is the criteria range for the first criterion, Shirt, which is in the H5 cell. Finally, the last two arguments denote the second criteria range and the second criterion respectively. So, the function will look for Shirt in the first criteria range and White in the second. Finally, it will return the total revenue from white shirts.
Read More: How to Apply SUMIF with Multiple Ranges in Excel
Conclusion
In this article, we have discussed three methods to use the SUMIF function with multiple criteria in different columns in Excel. These methods will help users to sum up their data based on multiple criteria and present a proper report to the spectators. These will also reduce efforts while summing up data fulfilling multiple criteria.
Related Articles
- SUMIF Across Multiple Sheets in Excel (3 Methods)
- How to Combine Excel SUMIF & VLOOKUP Across Multiple Sheets
- Use of the SUMIF Function across Multiple Columns in Excel (4 Methods)
- How to Sum Based on Column and Row Criteria in Excel (7 Ways)
- How to SUM with IF Condition in Excel (6 Suitable Examples)
