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

*with multiple criteria in different columns in Excel.*

**SUMIF****Table of Contents**hide

## SUMIF with Multiple Criteria in Different Columns in Excel: 3 Easy Ways

In this article, we will discuss * 3* handy ways to apply

*in different columns in Excel. We will use*

**SUMIF****with multiple criteria***with single criteria in the first method. Then, we will use the*

**the SUMIF function***function for multiple criteria. This method will have sub-methods where we will use*

**SUMIF****OR**logic, array formulas, and

*to sum with multiple criteria. Finally, we will opt for*

**the SUMPRODUCT function***with multiple criteria. Here is a sample dataset that we will use to demonstrate the methods.*

**the SUMIFS function**### 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
and type the formula below.**I4**

`=SUMIF($B$5:$B$16,H4,E5:E16)`

- Then, hit
.**Enter**

- We will have the total revenue of the
.**Shirt** - Repeat the process for
and**Pant**as well.**T-Shirt**

**Formula Breakdown:**

**SUMIF($B$5:$B$16,H4,E5:E16)**: The formula will go through rangeto look for the value in cell**B5:B16**which is shirt, and then sum all the values in the range**H4**which are associated with the value shirt and return the sum.**E5:E16**

**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

*function with*

**SUMIF****OR**logic. We will add multiple

*functions to fulfill multiple criteria and each value from each*

**SUMIF***function will be added to fulfill multiple criteria.*

**SUMIF****Steps:**

- Select cell
and enter the following formula.**J5**

`=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):**Thefunction will return the total sum of the revenues that are associated with the value in cell**SUMIF**which is shirt.**H5****SUMIF($B$5:$B$16,I5,$E$5:$E$16):**This will return the sum of the values associated with the value pants in therange.**E5:E16****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
and enter the following formula.**J5**

`=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 thefunction will scan through range*SUMIF*to look for shirts and pants and then sum up the profits in range**B5:B16**for those two products and return them as the input of the**F5:F16**function.*SUM***SUM(SUMIF($B$5:$B$16,{“Shirt”,”Pants”},F5:F16)):**Finally,will return the sum of the profit from those two products.**the SUM function**

#### 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
cell and insert the following formula.**J5**

`=SUM(SUMIF(B5:B16,H5:I5,F5:F16))`

- Then, hit the
button.**Enter**

- 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 rangeas our criteria. Then, the**H5:I5**function will go through range*SUMIF*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**B5:B16**function.*SUM***SUM(SUMIF(B5:B16,H5:I5,F5:F16)):**Finally, thefunction will sum the values returned by the*SUM*function for the two criteria values.*SUMIF*

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

**function.**

*SUM***Steps:**

- Choose cell
and enter the following formula.**J5**

`=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
and enter the following formula.**J5**

`=SUMIFS($E$5:$E$16,$B$5:$B$16,H5,$C$5:$C$16,I5)`

- Then, press the
button.**Enter**

- 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,, is the sum range of the function. In this case, the range denotes revenue. The second argument,**$E$5:$E$16**, is the criteria range for the first criterion, shirt, which is in cell**$B$5:$B$16****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.