Sum Multiple Columns Based on Multiple Criteria in Excel

You may need to calculate the sum of multiple columns. Once you know the methods it will seem as easy as you like. The agenda for today is showing you how to sum multiple columns based on multiple criteria.

First things first, let’s get to know about today’s practice workbook

Excel sheet - Sum Multiple Columns Based on Multiple Criteria

We have a relationship table of suppliers and their amount of sales made within three months in different cities.

This table contains dummy data. Things to be noted, this is a basic table, in real-life scenarios you may encounter many complex tables.

Practice Workbook

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

Sum Multiple Columns Based on Criteria

Based on Single Criteria

1. Using SUMIF function

If you have a single criterion then it will be a walk in the park for you while calculating the sum. All you need to do is use multiple SUMIF functions within OR logic.

SUMIF(range,criteria,sum_range1)+SUMIF(range,criteria,sum_range2)+... 

Here “+ works as OR logic. Each of the SUMIF functions produces a result and then the final result comes adding them together.

Write the function in Excel.

SUMIF single criteria - Sum Multiple Columns Based on Multiple Criteria

Here we calculated the sum from multiple columns where our criteria was the supplier Sunrise Wholesale. 

Read More: How to Total a Column in Excel (7 Effective Methods)

2. Using SUMPRODUCT

We can get the sum using the SUMPRODUCT function as well. If you want to know about the function broadly, please visit the Microsoft Support site.

The formula will be

SUMPRODUCT((criteria_range=criteria)*sum_range)

(criteria_range=criteria) returns an array of TRUE and FALSE. TRUE for match and FALSE for not match. Then gets multiplied with sum_range.

Write the formula in Excel.

SUMPRODUCT single criteria - Sum Multiple Columns Based on Multiple Criteria

We have shown you an example with supplier BryBelly as our criteria. And the formula provided the result we wanted.

Read More: Sum Every nth Column in Excel(Formula and VBA Code)

Based on Multiple Criteria

1. Using SUMIFS function

If you have heard about the SUMIFS function, then it’s obvious that it comes first to your mind while summing based on multiple criteria.

For a quick recap, the SUMIFS function allows you to sum cells that match multiple conditions.

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2], …)

sum_range – The range to be summed.

criteria_range1 – The first range to evaluate.

criteria1 – The criteria to use on range1.

criteria_range2 – The second range to evaluate.

criteria2 – The criteria to use on range2.

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

Read more: SUMIF Multiple Ranges

Let, we have two criteria, Supplier and CIty. We need to sum the amount that matches our criteria.

Multi criteria - Sum Multiple Columns Based on Multiple Criteria

Since we are aiming to apply conditions based on Supplier and City, let’s choose any of the suppliers and cities.

Example multiple - Sum Multiple Columns Based on Multiple Criteria

Here we have selected Sunrise Wholesale (Supplier) and New York (City). Now apply the SUMIFS formula counting these two as our criteria.  

SUMIF formula - Sum Multiple Columns Based on Multiple Criteria

Since we intend to find the total amount, our sum_range needs to be the whole. So, we inserted D4:F21 as sum_range. 

Supplier Name column as criteria_range1 and City column as criteria_range2. 

Error - Sum Multiple Columns Based on Multiple Criteria

Our formula didn’t work perfectly. It returned an error value. Do you know why?

The SUMIFS function doesn’t work for a multi-column sum_range. We provided the sum_range D4:F21. The range consists of multiple columns. So the formula didn’t work.

Now, what can we do?

We need to break down the sum_range. You may have heard – Divide and Conquer. Divide complex things into small simpler problems and then apply operations.

We will do the same here. We will divide the multi-column sum_range into a single column.

Our singled column sum_range will take part within the SUMIFS function. And to count all the columns we need to use multiple SUMIFS functions.

So our formula will be

SUMIFS(sum_range1, criteria_range1, criteria1, [criteria_range2], [criteria2], …) + SUMIFS(sum_range2, criteria_range1, criteria1, [criteria_range2], [criteria2], …)+ …

Write the formula in Excel.

multiple SUMIFS-Sum Multiple Columns Based on Multiple Criteria

Here we have written three SUMIFS functions. Within each SUMIF function, we have provided a single column sum_range. And it worked.

It gave the total Amount Sale by Sunrise Wholesale in New York city. Go through the data sets, you will find the total matches that our formula returned here.

Read More: How to Sum Entire Column in Excel (9 Easy Ways)

2. Using SUM Function

We can count the sum based on multiple criteria using the SUM function.

We are choosing Supplier and City as our criteria for showing examples.

SUM example -Sum Multiple Columns Based on Multiple Criteria

Here, Spare Vally and Los Angeles are our two example criteria.

Our SUM formula for multiple criteria will be something like

SUM((column_range1+column_range2+..)*(--(range1=criteria1))*(--(range2=criteria2))*..)

Write the formula in Excel.

SUM formula - Sum Multiple Columns Based on Multiple Criteria

Here we have inserted the three summing columns separating by “+“. This will give an array of a sum by each row.

(criteria_range = criteria) returns an array of TRUE and FALSE. Two unary operators (--) convert this array into an array of 1s and 0s.

We have included the criteria_ranges and criteria by "*". This worked as And function and multiplied the arrays.

Then the SUM function provided the result. Since it deals with arrays you need to use CTRL + SHIFT + ENTER to execute the formula.

Watch out for another example using BryBelly and San Fransisco as criteria

SUM example - Sum Multiple Columns Based on Multiple Criteria

Read More: How to Add Up Columns in Excel (12 Methods)

3. Using SUMPRODUCT function

We can do the task by SUMPRODUCT function as well. Our generic formula using the SUMPRODUCT will be something like below

SUMPRODUCT((criteria_range1=criteria1)*(criteria_range2=criteria2)*..*(sum_range))

(criteria_range=criteria) returns an array of TRUE and FALSE. TRUE for match and FALSE for not match.

Then all these arrays get multiplied together. And forms another array. This array then gets multiplied with the sum_range array.

Then SUMPRODUCT function sums the element of the array and produces the result.

Let’s see an example using this formula.

We are choosing Alibaba.co and West Hollywood as our criteria conditions.

Selected criteria

Write the formula in Excel.

SUMPRODUCT result

It gave the result we have wanted. You can go through the dataset and check whether the formula gave the right result or not.

Let’s see an example using more than two criteria. We have selected Supplier, Sales Person and City as our criteria.

Three criteria

Write the formula for this example.

Three criteria example

It gave the total amount of sell Mary did in New York city for Sunrise Wholesale. 

Before going into the real scenario do some practice of your own.

Read More: How to Sum Columns in Excel When Filtered (7 Ways)

Conclusion

That’s all for the article today. We have listed several formulas to sum multiple columns based on multiple criteria. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other formula or methods which 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