You may need to calculate the sum of multiple columns. Once you know the methods it will seem as easy as you like. This article will show you how to sum up multiple columns based on multiple criteria. I hope you find this article really interesting and it will help you to solve some difficult problems in the future.

## Download Practice Workbook

Download the practice workbook below.

## 3 Easy Methods to Sum Multiple Columns Based on Multiple Criteria in Excel

To sum up multiple columns based on multiple criteria, we have found three different methods through which you can have a clear idea of this topic. 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. Using this table, we would like to utilize **SUMIFS**, **SUM**, and **SUMPRODUCT** functions to get the desired solution.

### 1. Using Multiple SUMIFS Functions

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**. we have two criteria, Supplier and CIty. We need to sum the amount that matches our criteria. To understand the process, follow the steps carefully.

**Steps**

- We would like to sum up multiple columns based on multiple criteria. So, we take two criteria: Supplier and city.
- Using these two criteria, we will calculate the sum of multiple columns.
- To check whether the
**SUMIFS**function finds the solution or not, we take**Sunrise Wholesale**and**New York**. - Then, select cell
**K5**. - After that, write down the following formula.

`=SUMIFS(E5:E21,B5:B21,I5,D5:D21,J5)+SUMIFS(F5:F21,B5:B21,I5,D5:D21,J5)+SUMIFS(G5:G21,B5:B21,I5,D5:D21,J5)`

- Then, press
**Enter**to apply the formula.

**Note:**If we use this formula

**SUMIFS(E5:G22,B5:B22,I5,D5:D22,J5)**, we will get an error because the

**SUMIFS**function doesn’t work for a multi-column sum_range. We provided the sum_range

**E5:G22**. The range consists of multiple columns. So the formula didn’t work. That’s why 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.

**Read More: SUMIF with Multiple Criteria for Different Columns in Excel**

### 2. Applying SUM Function

We can count the sum based on multiple criteria using **the SUM function**. Using this function, we will get a similar result and get the sum of multiple columns based on multiple criteria. To understand the process, follow the steps.

**Steps**

- We would like to sum up multiple columns based on multiple criteria. So, we take two criteria: Supplier and city.
- Using these two criteria, we will calculate the sum of multiple columns.
- To check whether the
**SUM**function finds the solution or not, we take**BryBelly**and**San Fransisco**. - Then, select cell
**K5**. - After that, write down the following formula.

`=SUM((E5:G22)*(--(B5:B22=I5))*(--(D5:D22=J5)))`

- Then, press
**Enter**to apply the formula.

**Read More: Excel SUMIF Function for Multiple Criteria (3 Methods + Bonus)**

### 3. Utilizing SUMPRODUCT Function

We can also utilize **the SUMPRODUCT function** to sum multiple columns based on multiple criteria. Here, we will take two criteria and use them to get the sum of multiple columns. To understand the process, follow the steps carefully.

**Steps**

- We would like to sum up multiple columns based on multiple criteria. So, we take two criteria: Supplier and city.
- Using these two criteria, we will calculate the sum of multiple columns.
- To check whether the
**SUM**function finds the solution or not, we take**co**and**West Hollywood**. - Then, select cell
**K5**. - After that, write down the following formula.

`=<span style="font-size: 14pt; color: #000000;">SUMPRODUCT((E5:G22)*(B5:B22=I5)*(D5:D22=J5))</span>`

- Then, press
**Enter**to apply the formula.

**Read More:** **Use of the SUMIF Function across Multiple Columns in Excel (4 Methods)**

## 2 Ways to Sum Multiple Columns Based on Single Criteria in Excel

After finding the sum of multiple columns based on multiple criteria, we can also find the sum of multiple columns based on single criteria where we will add up several columns based on single criteria. In order to find the SUM of multiple columns based on single criteria, we have found two different solutions using the **SUMIF** and **SUMPRODUCT** functions.

### 1. Utilizing 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. To understand the process, follow the steps.

**Steps**

- We would like to add multiple columns based on single criteria. So, we take one criterion: Supplier.
- Using this criterion, we will calculate the sum of multiple columns.
- To check whether the
**SUM**function finds the solution or not, we take**Sunrise Wholesale**. - Then, select cell
**J5**. - After that, write down the following formula.

`=SUMIF(B5:B22,I5,E5:E22)+SUMIF(B5:B22,I5,F5:F22)+SUMIF(B5:B22,I5,G5:G22)`

- Then, press
**Enter**to apply the formula.

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

### 2. Applying SUMPRODUCT Function

Next, we can apply the **SUMPRODUCT** function to sum multiple columns based on a single criterion. This process is similar to the **SUMIF** function. To understand the process clearly, follow the steps.

**Steps**

- We would like to add multiple columns based on single criteria. So, we take one criterion: Supplier.
- Using this criterion, we will calculate the sum of multiple columns.
- To check whether the
**SUM**function finds the solution or not, we take - Then, select cell
**J5**. - After that, write down the following formula.

`=SUMPRODUCT((B5:B22=I5)*(E5:G22))`

- Then, press
**Enter**to apply the formula.

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

**Similar Readings**

**SUMIF Multiple Ranges [6 Useful Ways]****SUMIF Across Multiple Sheets in Excel (3 Methods)****How to Combine Excel SUMIF & VLOOKUP Across Multiple Sheets****SUMIF for Multiple Criteria Across Different Sheets in Excel (3 Methods)**

## Conclusion

That’s all for the article today. We have listed several formulas, to sum up, 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 formulas or methods which we might have missed here. Don’t forget to visit our **Exceldemy** page.