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.
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.
- 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:
- 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 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.
- 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 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.
- 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.
- 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.
- First, to calculate the total sales of 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 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,
- 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.
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.
- 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
- 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.
- 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)
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.