How to Sum Multiple Rows and Columns in Excel

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.

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:

• 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:

• 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  in cell E5:

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

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:

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

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:

• 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:

• 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