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.
SUMIF with Multiple Criteria in Different Columns in Excel: 3 Easy Ways
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:
- Select cell I4 and type the formula below.
=SUMIF($B$5:$B$16,H4,E5:E16)- Then, hit Enter.
- We will have the total revenue of the Shirt.
- Repeat the process for Pant and T-Shirt as well.
Formula Breakdown:
- SUMIF($B$5:$B$16,H4,E5:E16): The formula will go through range B5:B16 to look for the value in cell H4 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 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:
- Select cell J5 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.
- 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 cell H5 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:
- Choose cell J5 and enter the following formula.
=SUM(SUMIF($B$5:$B$16,{"Shirt","Pants"},F5:F16))- Hit Enter.
- As a result, we will get the total profits of shirts and pants.
Formula Breakdown:
- SUMIF($B$5:$B$16,{“Shirt”,”Pants”},F5:F16): Here the SUMIF function will scan through range B5:B16 to look for shirts and pants and then sum up the profits in range F5:F16 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 shirts and pants which were our intended criteria range.
Formula Breakdown:
- SUMIF(B5:B16,H5:I5,F5:F16): We will insert the values in the range H5:I5 as our criteria. Then, the SUMIF function will go through range B5:B16 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 shirts and 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:
- Choose cell J5 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 Sheet in Excel
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, sum the values in accordance with the criteria.
Steps:
- Choose cell J5 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 cell H5. Finally, the last two arguments denote the second criteria range and the second criterion respectively. So, the function will look for shirts 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
Download Practice Workbook
You can download the practice workbook here.
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.
