SUMIF Multiple Criteria Different Columns in Excel (3 Approaches)

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.

Excel sheet - Sumif Multiple Criteria Different Columns

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.

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.

Criteria - Sumif Multiple Criteria Different Columns

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.

SUMIF single criteria - Sumif Multiple Criteria Different Columns

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.

All criteria - Sumif Multiple Criteria Different Columns

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.

Multiple Criteria - Sumif Multiple Criteria Different Columns

Shirt and Pants within the same column.

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

Formula for multiple criteria - Sumif Multiple Criteria Different Columns

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.

Second example multiple criteria - Sumif Multiple Criteria Different Columns

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

Array sumif - Sumif Multiple Criteria Different Columns

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.

Sumif range array - Sumif Multiple Criteria Different Columns

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.

SUMPRODUCT - Sumif Multiple Criteria Different Columns

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.

Different columns - Sumif Multiple Criteria Different Columns

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.

Different columns - Sumif Multiple Criteria Different Columns

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

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

1st sumif - Sumif Multiple Criteria Different Columns

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

Second SUMIF- Sumif Multiple Criteria Different Columns

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

Total sumif

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

Non adjacent

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

Non adjacent result

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


Similar Readings:


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.  

SUMIFS

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

Write the formula in Excel.

SUMIFS result

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.

Three criteria

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.  

Sumifs with three criteria

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.


Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo