Shakil Ahmed
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.
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 Easy Methods)
Similar Readings
- How to Apply SUMIF with Multiple Ranges in Excel
- SUM with IF Condition in Excel (6 Suitable Examples)
- How to Add Up Columns in Excel (12 Methods)
- SUMIF Across Multiple Sheets in Excel (3 Methods)
- How to Sum Columns in Excel When Filtered (7 Ways)
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: How to Use SUMIF Function Across Multiple Columns in Excel
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: How to Sum Every Nth Column 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.
Related Articles
- How to Calculate Total Row and Column in Excel (4 Methods)
- Sum Columns by Color in Excel (6 Easy Methods)
- How to Add Two Columns in Excel (2 Easy Methods)
- Sum Based on Column and Row Criteria in Excel (7 Ways)
- How to Sum Columns in Excel Table (7 Methods)
- SUMIF and VLOOKUP Functions Across Multiple Sheets in Excel
- How to SUMIF for Multiple Criteria Across Different Sheet in Excel
