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.


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)

Using Multiple SUMIFS Functions to Sum Multiple Columns Based on Multiple Criteria

  • 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)))

Applying SUM Function to Sum Multiple Columns Based on Multiple Criteria

  • 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>

Utilizing SUMPRODUCT Function to Sum Multiple Columns Based on Multiple Criteria

  • 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)

Utilizing SUMIF Function to SUM Multiple Columns Based on Single Criteria in Excel

  • 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))

Applying SUMPRODUCT Function to SUM Multiple Columns Based on Single Criteria in Excel

  • Then, press Enter to apply the formula.

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


Similar Readings


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.


Further Readings

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo