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/calculate multiple **rows** and columns in Excel. Functions like **SUM **and **SUMPRODRUCT** will be used here in this article along with features like **Autosum**.

**Table of Contents**hide

## Download Practice Workbook

## 6 Effective Ways to Sum Multiple Rows and Columns in Excel

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.

### 1. Using AutoSum 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 there 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 month of**February**and**March**.

### 2. Applying SUMPRODUCT Function for 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 month of**February**and**March**.

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

### 3. Utilizing SUMPRODUCT Function for 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.

**Read More:** **How to Add Specific Cells in Excel (5 Simple Ways)**

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

You can add multiple columns by specifying the columns name instead of the cells 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”**.

**Read More:** **All the Easy Ways to Add up (Sum) a column in Excel**

**Similar Readings**

**How to Add Rows in Excel with Formula (5 ways)****Sum Cells in Excel: Continuous, Random, With Criteria, etc.****How to Sum a Column in Excel (6 Methods)****Sum by Font Color in Excel (2 Effective Ways)****How to Sum Values by Day in Excel (6 Methods)**

### 5. Finding Total 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 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.

**Read More: ****How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)**

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

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 Selected Cells in Excel (4 Easy Methods)**

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

**Read More:** **Shortcut for Sum in Excel (2 Quick Tricks)**

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