SUMIFS Sum Range Multiple Columns in Excel(6 Easy Methods)

Often, we come across instances where we need to sum range spreading multiple columns. In this article, we use functions such as SUM, SUMIF, SUMIFS, SUMPRODUCT as well as the combination of SUMPRODUCT, ISNUMBER, and SEARCH functions.

Suppose, in a dataset; Product Sale of different months and we want the total sale number of a specific product throughout the months.

Dataset-Sumifs Sum Range Multiple Columns

Dataset for Download

6 Easy Ways to Sumifs Sum Range Multiple Columns

Method 1: Using SUMIFS Function

Plain SUMIFS function’s syntax is

=SUMIFS (sum_range, criteria_range1, criteria1, [range2], [criteria2], …)

sum_range; declares the range we want to sum.

criteria_range1; defines the range where the criteria sit.

Criteria 1; set the criteria we look for in criteria_range1.

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 sumif the sum range of multiple columns.

Step 1: Add a helper column as Subtotal adjacent to the range. Type the below formula in cell I7.

=SUM(C7:H7)

Sumif function helper column-Sumifs Sum Range Multiple Columns

Step 2: Press ENTER and then Drag the Fill Handle and in a moment you will see the rest of the subtotal will appear.

sumif function helper column result

Step 3: Insert the following formula in any blank cell (i.e. C3).

=SUMIFS(I7:I27,B7:B27,B3)

I7:I27; is sum_range.

B7:B27; is the criteria_range1.

B3; is the criteria.

sumifs function-Sumifs Sum Range Multiple Columns

Step 3: Hit ENTER, the total Product Sale number of B3 (cell criteria Bean) will appear.

sumifs function result-Sumifs Sum Range Multiple Columns

Method 2: Using SUM Function

The syntax of the SUM function is

=SUM(number1, [number2],…)

Thus, we have to modify the SUM function as an array function to do the job.

Step 1: Insert the following formula in any blank cell (i.e.C3).

=SUM((C7:C27+D7:D27+E7:E27+F7:F27+G7:G27+H7:H27)*(–(B7:B27=B3)))

Here, in the formula

(C7:C27+D7:D27+E7:E27+F7:F27+G7:G27+H7:H27); defines the sum of individual six ranges.

(B7:B27=B3); declares the range value to be equal to B3 (Bean).

sum function-Sumifs Sum Range Multiple Columns

Step 2: Press CTRL+SHIFT+ENTER altogether, as it’s an array function. The total product sale of Bean appears.

sum function-Sumifs Sum Range Multiple Columns

You can use any name of the product in the B3 cell to count the total product sale.

Method 3: Using SUMIF Function

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

SUMIF(range, criteria, [sum_range])

range; declares the cells where criteria sit.

criteria; defines a condition to be applied in the range.

[sum_range]; declares the range we want to display.

Step 1: Add a helper column following Steps 1 and 2 described in Method 1.

Step 2: Type the following formula in any blank cell (i.e.C3).

=SUMIF(B7:B27,B3,I7:I27)

In the formula,

B7:B27; is the range.

B3; is criteria.

I7:I27; is sum_range.

sumif-Sumifs Sum Range Multiple Columns

Step 2: Press ENTER, The total number B3 (i.e.Bean) product sale emerges.

sumif function result

Method 4: Using SUM SUMIF Function

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 from Method 3, we have to sum individual columns applying the criteria every time. Let’s say, we want to sum the product sale in random months such as January, March and May.

Step 1: Enter the below formula in any blank cell (i.e.C3).

 =SUMIF(B7:B27,B3,C7:C27)+SUMIF(B7:B27,B3,E7:E27)+SUMIF(B7:B27,B3,G7:G27)

 In the formula,

SUMIF(B7:B27,B3,C7:C27); is a sum of product sale B3 product in B7:B27 range passing the value to sum from the C7:C27 range.

The rest of the additional threads represent the same purpose.

Sum sumif-Sumifs Sum Range Multiple Columns

Step 2: Tab ENTER, the total sale number of B3 (Bean) product appears.

sum sumif result-Sumifs Sum Range Multiple Columns

Method 5: Using SUMPRODUCT Function

The generic SUMPRODUCT formula is

 =SUMPRODUCT((criteria_rng=”text”)*(sum_range))

 Since we want the sum of 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.

 Step 1: Paste the following formula in any blank cell (i.e. B3)

=SUMPRODUCT((B7:B27=”Bean”)*(C7:H27))

 Inside the formula,

(C7:H27); returns the criteria as True or False.

(B7:B27="Bean")*(C7:H27); multiply the values with criteria output True or False.

In the end

SUMPRODUCT((B7:B27=”Bean”)*(C7:H27)); displays the total sale value.

sumproduct function-Sumifs Sum Range Multiple Columns

Step 2: Hit ENTER, the total number of sales of the product “Bean” will appear.

final sumproduct function-Sumifs Sum Range Multiple Columns

Method 6: Using SUMPRODUCT ISNUMBER SEARCH Function (Special Characters)

Sometimes, product names have special characters in their names. 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.

Step 1: Copy then Paste the following formula in any cell (i.e. B3).

=SUMPRODUCT((ISNUMBER(SEARCH(“Bean”,B7:B27)))*(C7:H27))

The formula works the same as described in Method 5, additionally, the ISNUMBER and SEARCH function do the job of ignoring any special characters in product names.

sumproduct isnumber search function-Sumifs Sum Range Multiple Columns

Step 2: Tab ENTER, the total sale number of “Bean” appears.

Final result sumproduct isnumber search function-Sumifs Sum Range Multiple Columns

Conclusion

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

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

ExcelDemy
Logo