How to Sum Multiple Rows and Columns in Excel

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 in cell 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.