How to Sum Rows in Excel (9 Easy Methods)

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!

Read More: Sum to End of a Column in Excel (8 Handy Methods)


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.

=C5+C6+C7+C8

You have got the total easily!

Read More: How to Sum Selected Cells in Excel (4 Easy Methods)


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.

Steps:

  • Enter the following formula in Cell C10.

=SUM(C5:C9)

  • Press Enter.

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

Read More: How to Sum a Column in Excel (6 Methods)

Example-2: Sum a Range of Cells

The steps are as follows.

Steps:

  • Type the following formula in Cell C10.

=SUM(C5:D9)

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

Steps:

  • Write the following formula in Cell C12.

=SUM(5:9)

  • Press Enter.

Now if you add more months’ data, you will get an automatic update for the total sales.

Read More: How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)


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.

Read More: How to Sum Multiple Rows and Columns in Excel


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.

Read More: How to Sum Between Two Numbers Formula in Excel


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.

Read More: Sum Formula Shortcuts in Excel (3 Quick Ways)


Similar Readings


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.

Read More: How to Add Specific Cells in Excel (5 Simple Ways)


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

Read More: How to Sum Filtered Cells in Excel (5 Suitable Ways)


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.

Type the below formula in cell B15:

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

Press Enter.

Read More: How to Add Multiple Cells in Excel (6 Methods)


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.

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

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

Read More: 3 Easy Ways to Sum Top n Values in Excel


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

Note: We can also use the AGGREGATE function to ignore the errors in the sum range.


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

Masum Mahdy

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