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.

**Table of Contents**hide

**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)**

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

** **

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

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

**=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)**.

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

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

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

**=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.

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

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

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

**=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.

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

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