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

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.

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.

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

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

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.

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

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

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

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

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

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

Similar Readings

• Excel SUMIFS with Multiple Vertical and Horizontal Criteria
• How to Use SUMIFS Function in Excel with Multiple Criteria
• SUMIFS with INDEX-MATCH Formula Including Multiple Criteria
• Exclude Multiple Criteria in Same Column with SUMIFS Function
• [Fixed]: SUMIFS Not Working with Multiple Criteria (3 Solutions)

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.

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

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

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.

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

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

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.

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

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

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

• Excel SUMIFS Not Equal to Multiple Criteria (4 Examples)
• How to Use SUMIFS with Date Range and Multiple Criteria (7 Quick Ways)
• SUMIFS with Multiple Criteria Along Column and Row in Excel
• SUMIFS with Multiple Criteria in the Same Column (5 Ways)
• SUMIF with Multiple Criteria in Column & Row in Excel (Both OR and AND Type)