How to Sum Rows in Excel (8 Methods)

In Microsoft Excel, the sum is one of the most common operations that is used everywhere. There are different techniques to sum up values in multiple rows in excel. Today we will see several methods.


Download Practice Workbook

You can download the following workbook and practice yourself.


8 Suitable Methods to Sum Rows in Excel

1. Add up (Sum) Cells in Multiple Rows in Excel with One Click

Suppose, You have numbers in different rows (Cell B5 to B9) within a single column (column B).

Add up (Sum) Cells in Multiple Rows in Excel with One Click

Just click on column letter B and look at the Excel Status Bar. You will find the sum there!

Add up (Sum) Cells in Multiple Rows in Excel with One Click


2. Sum Rows Using Basic Excel Formula for Addition

You can use the very basic excel formula to add numbers in different rows.

Sum Rows Using Basic Excel Formula for Addition

Just put an equal sign (=) in the cell where you want the sum and select the cells one by one by putting an addition sign (+) between two cells. Then press Enter.

Sum Rows Using Basic Excel Formula for Addition

You have got the total easily!

Sum Rows Using Basic Excel Formula for Addition


3. Get the Sum of Rows Using the SUM Function in Excel

Well, using the basic formula for adding cells in different rows can be tedious when there are a large number of inputs. In that case, we can use one of the most common excel functions, SUM.

Let’s see a few different uses of the SUM function in different cases to calculate the sum of multiple rows with examples.

i. Add up Multiple Rows to a Single Cell (Example 1, 2 & 3)

Example 1

Suppose you have just some numbers in multiple rows but in a single column and need to sum up them.

Add up Multiple Rows to a Single Cell (Example 1, 2 & 3)

Put the SUM function in the cell where you want to see the output. Type  =SUM( and then select the cells B5 to B9 by dragging the mouse or manually type B5:B9 and close the bracket. Then press Enter.

Add up Multiple Rows to a Single Cell (Example 1, 2 & 3)

Here you see, we have calculated the sum perfectly using the SUM function!

Add up Multiple Rows to a Single Cell (Example 1, 2 & 3)

Example 2

In this example, we have a table of numbers with 5 rows and 2 columns. We want to add all of them and put the summation in row Cell C10.

Add up Multiple Rows to a Single Cell (Example 1, 2 & 3)

To do that, type =SUM( in Cell C10. Select/manually write the cells B5 to C9. Close the bracket and press Enter.

Add up Multiple Rows to a Single Cell (Example 1, 2 & 3)

The output is here.

Add up Multiple Rows to a Single Cell (Example 1, 2 & 3)

 

Example 3

We need a way so that we get the sum automatically whenever we add new input numbers along row 5 to row 9. As in the example, we have added new inputs under the Number-3 header but the total is the same as in example 2.

Add up Multiple Rows to a Single Cell (Example 1, 2 & 3)

Here is how we can do that. Double click on cell C10. The function inside will appear.

Add up Multiple Rows to a Single Cell (Example 1, 2 & 3)

Delete the letters B and C from the formula.

Add up Multiple Rows to a Single Cell (Example 1, 2 & 3)

Then press Enter. This time, we have the new total.

Add up Multiple Rows to a Single Cell (Example 1, 2 & 3)


ii. Add up Rows Except for the Header

We have some numbers along different rows B5 to B9 in a single column B.

Add up Rows Except for the Header

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.

Add up Rows Except for the Header

Press Enter. The sum is there in cell C5.

Add up Rows Except for the Header


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

Add up Non-contiguous Cells in Different Rows

Type =SUM( in cell B11 and select the 1st, 3rd and 5th numbers, that is cells B5, B7, and B9.

Add up Non-contiguous Cells in Different Rows

Press Enter. There you have the total of the selected numbers.

Add up Non-contiguous Cells in Different Rows


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.

Use of AutoSum Command to Sum Rows in Excel

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.

Use of AutoSum Command to Sum Rows in Excel

You will see the cells B5 to B9 being selected.

Use of AutoSum Command to Sum Rows in Excel

Press Enter and you will get the sum.

Use of AutoSum Command to Sum Rows in Excel


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.

Sum Multiple Rows by Converting Data into Table

Select the range of cells B4 to D10 and go to Insert Tab > Click on Table > Press OK.

Sum Multiple Rows by Converting Data into Table

Click anywhere of the table and go to Table Design Tab. Turn on the Total Row option.

Sum Multiple Rows by Converting Data into Table

We have got the total marks of Henry in cell D10. But what about John’s mark? Don’t worry! It’s here.

Sum Multiple Rows by Converting Data into Table

Click on cell C10 and a dialogue box will appear. Select Sum from it.

Sum Multiple Rows by Converting Data into Table

Here is John’s total mark you have got.

Sum Multiple Rows by Converting Data into Table


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.

Sum Rows for Filtered Cells with Subtotal in Excel

Step 1: 

➤ Click anywhere in the table.

➤Go to Data Tab > Turn on the Filter option.

Sum Rows for Filtered Cells with Subtotal in Excel

Step 2: 

➤ Now click on the drop-down from the Student Header in the table.

➤ Then select the way you want to filter the table.

Sum Rows for Filtered Cells with Subtotal in Excel

Step 3: 

➤ We are going to add up the marks of Physics, Chemistry and Math only. So we have unselected Geography and Social Science. Press OK.

Sum Rows for Filtered Cells with Subtotal in Excel

Step 4: 

➤ Now, select the cells C10 and D10.

➤ Click on AutoSum.

Sum Rows for Filtered Cells with Subtotal in Excel

So we have calculated the marks in respective subjects for John and Henry separately.

Sum Rows for Filtered Cells with Subtotal in Excel


7. Combine SUM, MOD & ROW Functions to Sum Every K-th Row

Now we’ll see how we can sum every K-th (for example, every 2nd row) row in Excel using SUM, MOD, and ROW functions.

Combine SUM, MOD & ROW Functions to Sum Every K-th Row

Type the below formula in cell B15:

=SUM(B5:B14*MOD(ROW(B5:B14), 2))

Combine SUM, MOD & ROW Functions to Sum Every K-th Row

Press Enter.

Combine SUM, MOD & ROW Functions to Sum Every K-th Row


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.

Reckon the Largest Numbers in Multiple Rows Using SUM & LARGE Functions

Type the following formula in B15.

=SUM(LARGE(B5:B14, {1,2,3,4,5}))

Reckon the Largest Numbers in Multiple Rows Using SUM & LARGE Functions

Then, press Enter. So 2099 is the required sum.

Reckon the Largest Numbers in Multiple Rows Using SUM & LARGE Functions


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.

Sum Rows Containing Errors in Excel with SUM & IFERROR Functions

Type the following formula in cell B15:

=SUM(IFERROR(B5:B14, 0))

Sum Rows Containing Errors in Excel with SUM & IFERROR Functions

Press Enter. The errors are ignored and we have our resultant sum.

Sum Rows Containing Errors in Excel with SUM & IFERROR Functions


Conclusion

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.


Related Readings

Tags:

Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo