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

*in*

**SUMIF with multiple criteria in different columns***.*

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

*in different columns in*

**SUMIF****with multiple criteria***. We will use*

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

**the SUMIF function***. This method will have sub-methods where we will use OR logic, array formulas, and*

**the SUMIF function****for multiple criteria***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:**

- Firstly, select the
cell and type,**I4**

`=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
and**‘Pant’**as well.**‘T-Shir’**

**Formula Breakdown:**

**SUMIF($B$5:$B$16,H4,E5:E16)**: The formula will go through therange to look for the value in the**B5:B16**cell 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 the account. In this sub-method, we will use

*with OR logic. We will add multiple*

**the SUMIF function***to fulfill multiple criteria and each value from each*

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

**SUMIF function****Steps:**

- Firstly, select the
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**

- Consequently, we will sum up the values fulfilling multiple criteria.

**Formula Breakdown:**

**SUMIF($B$5:$B$16,H5,$E$5:$E$16):**will return the total sum of the revenues that are associated with the value in the**The SUMIF function**cell 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:**

- To begin with, choose the
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 summation of profits from the products
and**‘Shirt’**.**‘Pants’**

**Formula Breakdown:**

**SUMIF($B$5:$B$16,{“Shirt”,”Pants”},F5:F16):**Herewill scan through the range**the SUMIF function**to look for Shirt and Pants and then sum up the profits in the**B5:B16**range for those two products and return them as the input of**F5:F16****the SUM function.****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 Shirt and Pants which were our intended criteria range.

**Formula Breakdown:**

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

#### 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
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 Sheets in Excel (3 Methods)**

### 3. Using SUMIFS for Multiple Criteria

* The SUMIFS function* is

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

**Excel’s****Steps:**

- Firstly, choose the
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 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.**$B$5:$B$16**

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

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

**Excel**