Summing up multiple rows and columns in Excel is important for many purposes. In this article, we will see different techniques on how to sum multiple rows and columns in Excel. Functions like **SUM **and **SUMPRODRUCT** will be used here in this article along with features like **Autosum**.

We have taken a concise dataset for this tutorial to explain the steps clearly. This dataset has approximately 4 columns and 6 rows. We have the sales records for the 3 months for 5 products. We will sum this dataset in different row-wise and column-wise methods in the following section.

**Table of Contents**Expand

## 1. Using AutoSum Command to Sum Multiple Rows and Columns in Excel

Let`s say we have a worksheet that contains the monthly sales of different electrical appliances. Now, you wish to calculate the total monthly sales of all the products. You can easily do that by using the **AutoSum** feature. The procedure for performing the** AutoSum** feature is given below.

**Steps:**

- First, select the cell where you want to find the total sales amount. In our case, we want to find the total sales of January in cell
**C10**. In the**Formulas**tab under the**Editing**option, click on the**AutoSum**option, and then click on the**SUM**option.

- Now, after selecting the
**SUM**option you will see**the SUM function**appears in cell**C10**along with the range.

- Then, press
**Enter**to perform this**AutoSum**feature. - After that, drag this formulated cell to the right side in cells
**D10**and**E10**to find the total sales amount for the months of February and March.

## 2. Applying Excel SUMPRODUCT Function to Sum Multiple Columns

Instead of using the **AutoSum** feature, you can also use **the SUMPRODUCT function** directly to calculate the total sales for a month. For this follow the steps below:

**Steps:**

- First, go to cell
**C10**and insert the below formula:

`=SUMPRODUCT(C5:C9)`

- Next, after pressing
**Enter,**drag this formulated cell to cells**D10**and**E10**to calculate the total sales for the months of February and March.

Instead of the** SUMPRODUCT** function, you can also use the** SUM** function to perform this calculation.

**Read more: ****How to Sum Entire Column in Excel**

## 3. Inserting Excel SUMPRODUCT Function to Sum Multiple Rows

Now let`s say you want to find out the total sales of a specific product. In this case, we will be using the same formula but we will do the calculation row-wise.

**Steps:**

- To begin with, calculate the total sales amount of hair dryers using the following formula:

`=SUMPRODUCT(C5:D5)`

- Next, press
**Enter**to see the result. - Finally, drag down the formula
**using the Fill Handle**to find out the total sales of the other products.

## 4. Calculating Total by Defining Columns Range Instead of Cells Range

You can add multiple columns by specifying the column’s name instead of the cell range. Let`s say we want to find out the total sales of the products for the last 2 months. Here the months are in columns **C **and **D**.

**Steps:**

- First, type this formula
**E5**:

`=SUM($C:$D)`

- As a result, this will sum up all the values of columns
**C**and**D**. The usefulness of using this formula is that, whenever you place a new product name along with the sales value, it will get updated automatically if the new values are in this column range.

- After that, pressing
**Enter**you will get the below result.

**Note:**If you place this formula in the defined column range of the formula which is

**$C:$D**. The formula will not work as it creates a circular cell reference. It will give you the result

**“0”**.

## 5. Finding Sum by Defining Rows Range Instead of Cells Range

In the same way, you can find out the total sales of individual products by specifying the row range instead of the cell range in the formula. Let`s say you want to find the total sales of Hair Dryer, Blender, and Toaster.

**Steps:**

- First, to calculate the total sales of the Hair Dryer you can use the formula:

`=SUM(5:5)`

- Finally, in the same way, you can find the total sales of other products.

**Note:**Like before you can’t use this formula in the defined row range of the formula as it will create the circular cell reference. If you use this formula in the same row it will give a

**“0”**result.

## 6. Summing Data in Excel Table Using Total Row

Suppose the example that we are working on is now defined as a table in the worksheet. When you have a data table in your worksheet you can insert the **Total Row** option for summing up the data in a table. For inserting the **Total Row**, first,

**Steps:**

- To begin with, select any cell of the table, and in the
**Design**tab, check the**Total Row**under the**Table Style**option. - Immediately, you will see the sum value of columns
**C**,**D**, and**E**after selecting the**Total Row**option.

The most interesting part of this **Total Row** option is that you will get the drop-down list option for additional calculations at the end of your table. So, from this, you can easily find the total sales of month January, February and March.

**Read More: ****How to Sum Columns in Excel Table**

## How to Sum Multiple Rows in Excel Based on Criteria

If you want to sum the values which meet certain criteria, then you can follow the steps below.

**Steps:**

- First, go to cell
**C10**and type in this formula:

`=SUMIF($B$5:$B$9,$B$5,C5:C9)`

- Then, press
**Enter**and copy this formula to cells**D10**and**E10**. You should get the total Hair Dryer sales for each month.

## How to Sum Multiple Rows in Excel Using VLOOKUP

**The VLOOKUP function** can be used with the** SUM** function to sum multiple rows in Excel. Let us see how to do this.

**Steps:**

- To start with, navigate to cell
**C10**and insert the following formula:

`=SUM(VLOOKUP(B5,B5:E9,{2,3,4},FALSE))`

- Finally, press the
**Enter**key, and this sum all the Hair Dryer sales values.

## How to Sum Multiple Rows in Excel Using Shortcut

We can sum multiple rows in Excel using a shortcut. This actually applies the **AutoSum** feature from the keyboard. Follow the steps below to achieve this.

**Steps:**

- For this, click on cell
**C10**and press**Alt+=**. This will automatically suggest you a sum range. Press**Enter**to confirm the sum.

**Download Practice Workbook**

## Conclusion

From this article, we get to know about different processes and shortcuts on how to sum multiple rows and columns in Excel. Hope you will like this article. If you want to give any suggestions or find any difficulties in this article, please don’t hesitate to comment below.

## Related Articles

**How to Total a Column in Excel****How to Sum Every Nth Column in Excel****How to Sum Columns in Excel When Filtered****How to Sum Columns by Color in Excel****How to Calculate Total Row and Column in Excel**

**<< Go Back to Sum Columns | Sum in Excel**** | Calculate in Excel | Learn Excel**