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.

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

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

**Read more:** SUMIF Multiple Ranges

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.