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 for Download
6 Easy Ways to Sumifs Sum Range Multiple Columns
Plain SUMIFS function’s syntax is
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.
Step 2: Press ENTER and then Drag the Fill Handle and in a moment you will see the rest of the subtotal will appear.
Step 3: Insert the following formula in any blank cell (i.e. C3).
I7:I27; is sum_range.
B7:B27; is the criteria_range1.
B3; is the criteria.
Step 3: Hit ENTER, the total Product Sale number of B3 (cell criteria Bean) will appear.
Method 2: Using SUM Function
The syntax of the SUM function is
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).
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).
Step 2: Press CTRL+SHIFT+ENTER altogether, as it’s an array function. The total product sale of Bean appears.
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
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).
In the formula,
B7:B27; is the range.
B3; is criteria.
I7:I27; is sum_range.
Step 2: Press ENTER, The total number B3 (i.e.Bean) product sale emerges.
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).
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.
Step 2: Tab ENTER, the total sale number of B3 (Bean) product appears.
Method 5: Using SUMPRODUCT Function
The generic SUMPRODUCT formula is
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)
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.
Step 2: Hit ENTER, the total number of sales of the product “Bean” will appear.
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).
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.
Step 2: Tab ENTER, the total sale number of “Bean” appears.
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.