How to 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. 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.

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

To sum up multiple columns based on multiple criteria, we have found 3 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 as the supplier and New York as the city.
Select cell K5 and 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 as 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.

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 as the supplier and San Fransisco as the city.
Then, write down the following formula on cell K5.

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

Then, press Enter to apply the formula.

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 SUMPRODUCT function finds the solution or not, we take Alibaba.co as the supplier and West Hollywood as the city.
Copy and paste the following formula on cell K5.

=SUMPRODUCT((E5:G22)*(B5:B22=I5)*(D5:D22=J5))

Then, press Enter to apply the formula.

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 SUMIF function finds the solution or not, we take Sunrise Wholesale.
Then, select cell J5 and 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.

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 SUMPRODUCT function finds the solution or not, we take BryBelly.
Write down the following formula on cell J5.

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

Then, press Enter to apply the formula.

Download Practice Workbook

Download the practice workbook below.

Sum Multiple Columns Based on Multiple Criteria.xlsx

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