In Microsoft Excel, the Sum is one of the most common operations we use. There are different techniques to sum up values in multiple rows in excel. Today we will see several methods with suitable examples and proper illustrations.
Download Practice Workbook
You can download the following workbook and practice yourself.
9 Suitable Methods to Sum Rows in Excel
1. Get to Know the Sum with One Click
If all of the values are in column, then just click on column letter B and look at the Excel Status Bar. You will find the sum there!
2. Use Basic Addition Formula to Sum Rows
You can use the very basic addition formula to add numbers in different rows.
Enter the following formula in Cell C5 and press Enter. Then drag the fill handle to the right side to copy the formula.
You have got the total easily!
Read more: How to Add Numbers in Excel.
3. Get the Sum of Rows Using the SUM Function in Excel
Let’s see different uses of the SUM function in different cases to calculate the sum of multiple rows with examples.
3.1 Add up Multiple Rows to a Single Cell
Example-1: Sum Values in a Single Column
Execute the following steps.
- Enter the following formula in Cell C10.
- Press Enter.
Here you see, we have calculated the sum perfectly using the SUM function!
Example-2: Sum a Range of Cells
The steps are as follows.
- Type the following formula in Cell C10.
- Press Enter.
Example-3: Sum of Increasing Multiple Rows
Need a way to get the sum automatically whenever you add new input along the rows? This example will show the trick.
We have the Sales along 5th, 6th, 7th, 8th, and 9th row. Now follow the steps below.
- Write the following formula in Cell C12.
- Press Enter.
Now if you add more months’ data, you will get an automatic update for the total sales.
3.2 Add up Rows Except for the Header
We have some numbers along different rows B5 to B9 in a single column B.
Since we want all the values in Column B except the header, we need to input the SUM function elsewhere other than in column B. Here, it’s in cell D5.
Now, type =SUM( and select from cell B5. Then type B1048576. Close the bracket.
Press Enter. The sum is there in cell C5.
3.3 Add up Non-contiguous Cells in Different Rows
This time we won’t add up all the numbers, rather we’ll make a sum of the values from the 1st, 3rd and 5th rows only.
Type =SUM( in cell B11 and select the 1st, 3rd and 5th numbers, that is cells B5, B7, and B9.
Press Enter. There you have the total of the selected numbers.
4. Use of AutoSum Command to Sum Rows in Excel
There is an easier tool in Excel for you. You don’t need to select the cells yourself to add up if you apply the AutoSum command.
AutoSum Shortcut: ALT + =
Follow the flowchart. Click just below the range of cells. Then go to the Formula Tab as the picture suggests. Then go to the AutoSum command. Click on Sum.
You will see the cells B5 to B9 being selected.
Press Enter and you will get the sum.
5. Sum Multiple Rows by Converting Data into Table
Using the Table tool is another useful way of summing rows in Excel. Here is an example. We want to calculate the total marks of John and Henry.
Select the range of cells B4 to D10 and go to Insert Tab > Click on Table > Press OK.
Click anywhere on the table and go to Table Design Tab. Turn on the Total Row option.
We have got the total marks of Henry in cell D10. But what about John’s mark? Don’t worry! It’s here.
Click on cell C10 and a dialogue box will appear. Select Sum from it.
Here is John’s total mark you have got.
6. Sum Rows for Filtered Cells with Subtotal in Excel
Subtotal is another instrumental tool for the summation of cells in different rows. Let’s see an example.
➤ Click anywhere in the table.
➤Go to Data Tab > Turn on the Filter option.
➤ Now click on the drop-down from the Student Header in the table.
➤ Then select the way you want to filter the table.
➤ We are going to add up the marks of Physics, Chemistry and Math only. So we have unselected Geography and Social Science. Press OK.
➤ Now, select the cells C10 and D10.
➤ Click on AutoSum.
So we have calculated the marks in respective subjects for John and Henry separately.
7. Combine SUM, MOD & ROW Functions to Sum Every K-th Row
Type the below formula in cell B15:
Read more: Sum Formula Shortcuts in Excel
8. Reckon the Largest Numbers in Multiple Rows Using SUM & LARGE Functions
We can sum top 2/3/4 or 5 (as many as we wish) numbers using SUM and LARGE functions in Excel. Here is an example. We have to calculate the top 5 largest numbers from the following list.
Type the following formula in B15.
Then, press Enter. So 2099 is the required sum.
9. Sum Rows Containing Errors in Excel with SUM & IFERROR Functions
We can sum values in rows even though they have errors. We will incorporate SUM and IFERROR functions for this purpose.
Look at the table. It has two errors in it. We have to add up the other values.
Type the following formula in cell B15:
Press Enter. The errors are ignored and we have our resultant sum.
Note: We can also use the AGGREGATE function to ignore the errors in the sum range.
Hope you will find all these methods instrumental. If you have any questions, comments, or any kind of feedback, please let me know in the comment box.