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.

**Table of Contents**hide

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

`=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)`

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

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

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

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

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

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

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

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

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

`=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

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