Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

`=SUMIFS(I6:I26,B6:B26,B29)` ### 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). • 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.

### 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. • Press Enter, The total number B29 (i.e.Bean) product sales emerges. ### 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. • Press Enter and the total sale number of B29 (Bean) products appears. ### 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. • Then, press Enter, the total number of sales of the product “Bean” will appear. ### 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. • 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 #### 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 