The sample dataset contains the sales records of 5 products over 3 months. We will sum this dataset by rows and columns.

## Method 1 – Using the AutoSum Command to Sum Multiple Rows and Columns in Excel

**Steps:**

- Select the cell where you want to find the total sales amount. 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.

**The SUM function**appears in cell**C10**along with the range.

- Press
**Enter**. - Drag the Fill Handle to the right side in cells
**D10**and**E10**to find the total sales amount for the months of February and March.

## Method 2 – Applying the SUMPRODUCT Function to Sum Multiple Columns

**Steps:**

- Go to cell
**C10**and insert the following formula:

`=SUMPRODUCT(C5:C9)`

- Hit
**Enter**and drag the Fill Handle to the right for the other two cells.

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

## Method 3 – Inserting the SUMPRODUCT Function to Sum Multiple Rows

We removed the March column for this example.

**Steps:**

- Calculate the total sales amount of hair dryers using the following formula:

`=SUMPRODUCT(C5:D5)`

- Press
**Enter**to see the result. - Drag down the formula
**using the Fill Handle**to find out the total sales of the other products.

## Method 4 – Calculating Total by Defining Columns Range Instead of Cells Range

We want to find out the total sales of the products for the last 2 months. The months are in columns **C **and **D**.

**Steps:**

- Use this formula
**E5**and hit Enter.

`=SUM($C:$D)`

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.

- Here’s the result.

**Note:**The formula from this example can’t be placed in cells along the C or D column as it would create a circular reference.

## Method 5 – Finding the Sum by Defining Rows Range Instead of Cells Range

We want to find the total sales of Hair Dryer, Blender, and Toaster.

**Steps:**

- To calculate the total sales of the Hair Dryer you can use the formula:

`=SUM(5:5)`

- Use AutoFill to find the total sales of other products.

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

## Method 6 – Summing Data in an Excel Table Using a Total Row

**Steps:**

- Convert the dataset to a table (Ctrl + T and select the range properly).
- Select any cell of the table, and in the
**Design**tab, check the**Total Row**under the**Table Style**option. - You will see the sum value of columns
**C**,**D**, and**E.**

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

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

**Steps:**

- Go to cell
**C10**and use this formula:

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

- 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

**Steps:**

- Navigate to cell
**C10**and insert the following formula:

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

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

## How to Sum Multiple Rows in Excel Using a Shortcut

**Steps:**

- Click on cell
**C10**and press**Alt + =**. - This will automatically suggest a sum range. Press
**Enter**to confirm the sum.

**Download the Practice Workbook**

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