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

## 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. Here we calculated the sum from multiple columns where our criteria was the supplier Sunrise Wholesale.

#### 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. We have shown you an example with supplier BryBelly as our criteria. And the formula provided the result we wanted.

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

Let, we have two criteria, Supplier and CIty. We need to sum the amount that matches our criteria. Since we are aiming to apply conditions based on Supplier and City, let’s choose any of the suppliers and cities. Here we have selected Sunrise Wholesale (Supplier) and New York (City). Now apply the SUMIFS formula counting these two as our 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. 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. 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.

#### 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. 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. 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 #### 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. Write the formula in Excel. 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. Write the formula for this 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.

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