Different circumstances may demand the calculation of the sum of cells using multiple criteria. Today, we will show you calculate the sum using the SUMIF functions with multiple criteria on different columns.

For conducting the session, we are going to use Excel 2019. You can choose your version.

First things first, let’s get to know about the practice workbook of today’s session.

We are using a basic table of garments. There are five columns; *Products, Color, Size, Revenue, Profit. *

Using this relationship we will see how the **SUMIF** function will work for single and multiple criteria.

This is a basic table consisting of some dummy data, in a practical scenario, your data sets may be much complex.

**Table of Contents**hide

## Practice Workbook

You are welcome to download the practice workbook from the link below.

## SUMIF Multiple Criteria Different Columns

### 1. SUMIF for Single Criteria

When you need to sum using a single criterion, you can do that quite easily **using the SUMIF function**.

Let’s get to know about the **SUMIF **function first. The **SUMIF** function returns the sum of the values in a range that meets a single criterion.

Syntax of the **SUMIF** function is as follows

`SUMIF (range, criteria, [sum_range])`

**range: **The range of cells that you want to be evaluated by criteria

**criteria: **Determining the condition of which cells to add

**sum_range: **The cells to add together. This is an optional field.

You want to know more about the **SUMIF **function, visit Microsoft Support site.

If your *range *is not in number format, then usually you have to provide a number formatted *sum_range*.

Have a look at our example scenario, we are going to count the total revenue for each of our products.

We have inserted the unique product names separately from the original table. Each of our items will act as criteria.

Your range should be the cells where you have the chance to match the criteria.

In our example, if you choose the *Color *column and your criteria are *Shirt, *then all your effort will go in vain.

You need to choose the *Products *column when you are searching for a *Shirt *or *Pants. *

Let’s write the **SUMIF** function in Excel.

We have written the formula for finding the total revenue for *Shirts. *Inside the formula, we have inserted the *Products *column as *range,* *Shirt *as criteria, and the *Revenue *column as *sum_range. *

It gave the total. Since our dataset is not a big one, go through a bit and you will see that for *Shirt *a total of $106 comes.

Write the formula for other products as well.

### 2. SUMIF for Multiple Criteria

The **SUMIF **function can also be useful while evaluating multiple options. Your evaluation can be done within a single column or multiple columns may come into play.

#### 2.1. Within Single Column

##### 2.1.1. Using OR logic

The simplest method of using the **SUMIF** function for multiple criteria is using **OR** logic.

In this method, we will use a couple of **SUMIF **functions.

`SUMIF(range,criteria,[sum_range]) + SUMIF(range,criteria,[sum_range])+... `

The more criteria you need to match, the more **SUMIF **functions you need to add together.

This `'+'`

works as the **OR **logic. Any of the **SUMIF **functions return a result it will take that within the calculation.

Here we are taking *Shirt *and *Pants *two criteria to show you the example.

*Shirt *and *Pants *within the same column.

Our approach will be writing separate **SUMIF **functions for each of the criteria.

Here, we have written two **SUMIF **functions and add them.

Both the functions return their respective results and show the result combinedly, irrespective of any common between them.

Our example here, providing the total revenue of *Shirt *+ *Total revenue of Pants. *

We can choose other options as well like the image below.

##### 2.1.2. Using Array with the SUM function

The previously discussed **OR **formula is a simple one, but it may monotonous when you have more than two or three criteria. To eliminate the problem we can introduce an array of criteria.

Let, you need to check for *Shirt *and *Pants. *You need to set these two within an array

`{"Shirt","Pants"} `

Only using the **SUMIF **function will not lead to the correct result. We need to insert the **SUMIF **function within a **SUM **function.

Our formula will be something like this

`SUM(SUMIF(range,{array},sum_range))`

Here the **SUMIF **function will return another array, that contains the sum matching the criteria.

Then the **SUM **function will add the elements of that array and provide the result.

Instead of using the array manually, you can provide a range also. That might be suitable when you have a bunch of values to check.

`SUM(SUMIF(range,criteria_range, sum_range)) `

These types of formulas are called array formulas. It traverses within range and returns arrays.

Let’s write the formula in Excel.

This formula behaves the same as the previous one. You need to use **CTRL + SHIFT + ENTER **to generate results using this formula.

##### 2.1.3. Using Array with SUMPRODUCT function

We can use **the SUMPRODUCT function** as well while using an array of criteria. The formula will be something like as below

`SUMPRODUCT(SUMIF(range,criteria_range,sum_range))`

Write the formula in Excel.

In this example, we have *Shirt *and *T-Shirt *as our two criteria and we are calculating their total profit.

You may wonder why we are using the **SUMPRODUCT** since the same thing can be done by the **SUM** function.

**SUMPRODUCT** works better while dealing with arrays.

#### 2.2. Different Column References

For your calculation, often you may need to utilize multiple columns. For that, you need to use separate **SUMIF** functions. Each of the **SUMIF **functions will be used for matching the criteria within each column

Our formula will be

`SUMIF(range_column1,criteria,sum_range)+SUMIF(range_column2,ciretia,sum_range)+.. `

You may find the formula familiar. Yes this our earlier discussed **OR **logic formula.

Then we had shown the application of the formula on a single column.

This time we will apply this formula on two different columns.

For example purpose, we have chosen a product *Shirt *and a color *White. *We will see how things work on different columns.

Write the formula in Excel.

Here our final result consists of the result from two individual **SUMIF **functions.

1st **SUMIF** function returns the total revenue for *Shirt(s).*

The second **SUMIF** function returns the total revenue of products that are White.

Then the total will be the output since we have added them to our formula.

You can use this formula for non-adjacent or non-contiguous columns as well.

Here we have selected an item from the *Products *column and *Size *column respectively. The columns are non-contiguous.

Write the formula for this example

The working mechanism is the same as earlier. One **SUMIF **function returns results for *Pants *and another one returns for size *M. *

## Further Readings:

**SUMIF with Multiple Criteria in Column & Row in Excel (Both OR and AND Type)****SUMIF with Multiple Criteria (5 Easiest Examples)**

### 3. SUMIFS for Multiple Criteria

It may not be an ideal one to add numbers of **SUMIF **functions while checking multiple criteria on different columns. And your intention could be to get results that match all the criteria. Then one function that can help you there is **SUMIFS. **

From the name, you might have assumed that it will check multiple conditions. Your assumption is right.

**SUMIFS** function sums cells that meet multiple criteria. The syntax for the **SUMIFS** function

`SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...)`

**sum_range – **The range to be summed.

**range1 – **The first range to evaluate.

**criteria1 – **The criteria to use on range1.

**range2 –** The second range to evaluate.

**criteria2 – **The criteria to use on range2.

*range2 and criteria2 are optional fields. If you have multiple ranges/criteria then use these fields. And you can insert as many criteria as you want. *

You want to know more about the **SUMIFS **function, visit Microsoft Support site.

We are choosing a product *Shirt *and color *White *as our 1st and second criteria. * *

We have two criteria from two different columns. There will be two different ranges.

Write the formula in Excel.

The first range here is the sum_range, *Revenue *column.

Then *Products *column as range1 since our criteria1(Shirt) was within this column.

The *Color *column and *White *color have been set as range2 and criteria2 respectively.

The criteria within the **SUMPRODUCT **function behave like product calculation. It returns the sum of that cells which match both (all) the conditions.

The **SUMPRODUCT **function first evaluates the rang1 and criteria1, it’s the first filter, here once it found that our preference was Shirt it filtered for the rows that match the criteria.

After checking the first criteria it moves on to the second one and filters out by that.

Then the function applies sum operation on the cells that have been found after filtering with the conditions.

Let’s see another example.

Here are criteria were *Pants, Black *and* M. *We didn’t have any *M-sized* *Black *color *Pants *inside our table. So it provides *$0. *

Let’s see another example for L size Black Pants. * *

These criteria found data inside our table and returned the sum here.

## Conclusion

That’s all for today. We have tried listing several methods using **SUMIF** with multiple criteria on different columns. Hope you will find this article helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods that we might have missed here.