SUMIFS: Sum Range Across Multiple Columns (6 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Often, we come across instances where we need to SUMIFS: sum a range across multiple columns. In this article, we use functions such as SUM, SUMIF, SUMIFS, and SUMPRODUCT, as well as the combination of SUMPRODUCT, ISNUMBER, and SEARCH functions. For the demonstration of the methods, we have a dataset of product sales for different months. Furthermore, we want to know the total number of sales for a specific product over the course of the month. The following image shows an overview of the methods.

Overview of Sumifs sum range across multiple columns


How to Use SUMIFS to Sum Range of Multiple Columns: 6 Easy Ways


Method 1: Using SUMIFS Function

The nature of the SUMIFS function is that it can only sum one column depending on criteria that are sitting in multiple columns. Therefore, we have to add a helper column to sum the range of multiple columns.

Steps:

  • Firstly, add a helper column as Subtotal adjacent to the range.
  • Secondly, type the below formula in cell I6.

=SUM(C6:H6)

Formula to create the subtotal

  • Thirdly, press Enter and then drag the Fill Handle, and in a moment you will see the rest of the subtotal appear.

Creating helper column

  • Then, insert the following formula in cell C29 and hit Enter. Here, I6:I26 is sum_range, B6:B26 is the criteria_range1, and B29 is the criteria.
  • So, the total Product Sale number of B29 (cell criteria Bean) will appear.

=SUMIFS(I6:I26,B6:B26,B29)

Output of the first method

Read More: Excel SUMIFS with Multiple Vertical and Horizontal Criteria


Method 2: Using SUM Function

In this section, we will have to modify the SUM function as an array function to achieve the objective of this article.

Steps:

  • Insert the following formula in cell C29.

=SUM((C6:C26+D6:D26+E6:E26+F6:F26+G6:G26+H6:H26)*(--(B6:B26=B29)))

  • Here, in the formula (C7:C27+D7:D27+E7:E27+F7:F27+G7:G27+H7:H27); defines the sum of individual six ranges. Then, (B7:B27=B3); declares the range value to be equal to B3 (Bean).

Using SUM function to sum range across multiple columns

  • Press Enter. The total product sale of Bean appears. However, for earlier versions of Excel, press Ctrl+Shift+Enter.

Output of method 2

  • You can use any name of the product in the B29 cell to count the total product sales.

Method 3: Utilizing SUMIF Function

As we know from earlier, the SUMIF function does not allow summing ranges from multiple columns at once. But we can use a helper column to execute what we need.

Steps:

  • Add a helper column, as shown in Method 1.
  • Type the following formula in cell C29.

=SUMIF(B6:B26,B29,I6:I26)

  • In the formula, B6:B26 is the range, B29 is the criteria, and I6:I26 is the sum_range.

SUMIF formula to sum range across multiple columns

  • Press Enter, and The total number B29 (i.e.Bean) product sales emerge.

Output of method 3

Read More: How to Use SUMIFS with Multiple Criteria in the Same Column


Method 4: Applying Multiple SUMIF Functions

Another way to use the SUMIF function is to individually sum one range at a time. This could be hideous work, but if you have a few columns to execute, you can apply it. As we know the syntax of the SUMIF function, we have to sum individual columns, applying the criteria every time. Let’s say we want to sum the product sales in random months such as January, March, and May.

Steps:

  • Firstly, enter the below formula in cell C29.

=SUMIF(B6:B26,B29,C6:C26)+SUMIF(B6:B26,B29,E6:E26)+SUMIF(B6:B26,B29,G6:G26)

  • In the formula, SUMIF(B6:B26,B29,C6:C26); is a sum of product sales for B3 products in the B6:B26 range, passing the value to sum from the C6:C26 range. The rest of the additional threads serve the same purpose.

Multiple SUMIF functions to sum a range of cells

  • Press Enter and the total sale number of B29 (Bean) products appears.

Output of method 4

Read More: How to Apply SUMIFS with Multiple Criteria in Different Columns


Method 5: Using SUMPRODUCT Function

Since we want the sum of the total sales of a particular product, we can use the product name as a ”text” reference. And the formula will show the sum from the sum_range. For this procedure, we will use the SUMPRODUCT function.

Steps:

  • Firstly, use the following formula in cell C29.

=SUMPRODUCT((B6:B26=B29)*(C6:H26))

  • Inside the formula, (C6:H26); returns the criteria as True or False. (B6:B26=B29)*(C6:H26); multiply the values with the criteria and output True or False.
  • In the end SUMPRODUCT((B6:B26=B29)*(C6:H26)); displays the total sale value.

SUMPRODUCT formula

  • Then, press Enter, and the total number of sales of the product “Bean” will appear.

Output of method 5

Read More: SUMIFS with Multiple Criteria Along Column and Row in Excel


Method 6: Combining SUMPRODUCT, ISNUMBER, and SEARCH Functions

Sometimes, product names have special characters in them. These characters get input from incautious users. In that scenario, we can use a combination of SUMPRODUCT, ISNUMBER, and SEARCH to count the total sales of any product.

Steps:

  • To begin with, copy then paste the following formula in cell B29.

=SUMPRODUCT((ISNUMBER(SEARCH(B29,B6:B26)))*(C6:H26))

  • The formula works the same as described in Method 5. Additionally, the ISNUMBER and SEARCH functions do a good job of ignoring any special characters in product names.

Combination formula to sumifs sum range across multiple columns

  • Finally, press Enter, and the total sale number of “Bean” appears.

Output of method 6


Download Practice Workbook

You can download the Excel file from the link below.


Conclusion

The SUM, SUMIF, and SUMIFS functions sum a range in multiple columns with some modifications in the formulas in Excel. The SUMPRODUCT function does the job with ease after we add criteria to the formula. The combination of the SUMPRODUCT, ISNUMBER, and SEARCH functions can sum the total sale despite special characters existing in the product names. I hope you find the discussed methods lucid enough to follow. And comment, if you need further clarifications or have something to add.


Related Articles


<< Go Back to Excel SUMIFS with Multiple Criteria | Excel SUMIFS Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo