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


Download Practice Workbook

You can download the Excel file from the link below.


6 Easy Ways to Sum Range Across Multiple Columns for SUMIFS in Excel

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 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 Sum Ranges and Multiple 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 sale.

Read More: How to Use VBA Sumifs with Multiple Criteria in Same Column


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, The total number B29 (i.e.Bean) product sales emerges.

Output of method 3

Read More: How to Use SUMIFS Formula with Multiple Criteria in Excel (11 Ways)


Similar Readings


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: SUMIFS Multiple Criteria Different Columns (6 Effective Ways)


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.

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, the total number of sales of the product “Bean” will appear.

Output of method 5

Read More: How to Use SUMIFS When Cells Are Not Equal to Multiple Text


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


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

Maruf Islam
Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo