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


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.

how to sum multiple rows and columns in excel


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.

AutoSum to sum multiple rows and columns in excel

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

AutoSum to sum multiple rows and columns in excel

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

SUMPRODUCT function to sum multiple rows and columns in excel

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

defining column range to sum multiple rows and columns in excel

  • 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


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)

defining row range to sum multiple rows and columns in excel

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

total row to sum multiple rows and columns in excel

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.


Related Articles

Siam Hasan Khan

Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo