How to Sum Rows in Excel (9 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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 practice workbook from here.


9 Quick Methods to Sum Rows in Excel

Here, we will show 9 quick methods with steps to Sum Rows in Excel. We will use several built-in functions and different features of Excel to sum rows.

1. Sum Rows with One Click in Excel

If all of the values are in a column, then just select the column. Here, we select column B and look at the Excel Status Bar. You will find the sum there.

how to sum rows in excel

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


2. Use Simple Formula to Sum Rows

You can use the very basic addition formula to add numbers in different rows. Let’s follow the steps given below.

  • Firstly, write the following formula in Cell B11 and press Enter.
=B5+B6+B7+B8+B9
  • Finally, we will see the sum value of Cell range B5:B9 in Cell B11.

Use Simple Formula to Sum Rows

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


3. Utilize SUM Function of Excel to Add Rows

Let’s see different uses of the SUM function in different cases to calculate the sum of multiple rows with examples. The stepwise procedures are given in this section.


3.1 Add Multiple Rows to a Single Cell

  • Firstly, write the following formula in Cell B11.
=SUM(B5:B9)
  • Then, press Enter.
  • Finally, we will see the sum value of elements from Column B in Cell B11.

Add Multiple Rows to a Single Cell

Instead of a single column, we can also sum up a range of cells.

  • Primarily, write the following formula in Cell C10 and press Enter.
=SUM(B5:C9)
  • Consecutively, we will see the sum of the range B5:C9 in Cell C10.

We can adjust the last formula a bit for taking more numbers from the rows.

  • Only, change the formula a little. The adjusted formula is given below.
=SUM(5:9)
  • Thus, we can add more numbers in those rows and the value will be added in Cell C10.

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


3.2 Add up Rows Except for Header

Now, we will change the range of the formula such that, it gives the summation value for every element below the header.

  • Firstly, write the following formula in Cell C5.
=SUM(B5:B1048576)
  • Also, press Enter.

Add up Rows Except for Header

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


3.3 Addition of Non-Contiguous Cells

We can also add non-contiguous cells using the SUM function. For that, follow the steps given below.

  • Firstly, write the following formula in Cell B11.
=SUM(B5,B7,B9)
  • Then, hit Enter and we will see the sum of values from Cells B5, B7 and B9 in Cell B11.
  • Of course, you can use different cell references to get their sum value.

Addition of Non-Contiguous Cells

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


4. Apply AutoSum Feature to Sum Rows

We can apply the AutoSum feature of Excel to sum values from rows. Let’s walk through the procedures.

  • Firstly, select Cell B10 where we want the sum value of the upper cells of the same column.
  • Then, go to the Formula tab and select AutoSum > Sum.

Apply AutoSum Feature to Sum Rows

  • Finally, we will see the sum value of cells above Cell B10.

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


Similar Readings


5. Sum Multiple Rows Utilizing Table in Excel

We can also utilize the table feature of Excel to sum values of rows. Follow the stepwise procedures given below.

  • Firstly, select the whole range of dataset.
  • Then, go to the Insert tab and select Table.

Sum Multiple Rows Utilizing Table

  • Consecutively, a Create Table window will appear. Just, hit OK.

  • Afterward, select Cell D10 to get the sum value there. Rightclick on it.
  • Further, select Table > Total Rows from the context menu.

  • Thus, the sum for Column D is already done.
  • Now, in the bottom empty cell of Column C.
  • Here, move to Cell C10, and click on the small drop-down icon.
  • Later on, select Sum from the options.

  • Finally, we will see the total sum value of Column C there.

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


6. Sum Filtered Rows Using SUBTOTAL Function in Excel

This time we will use the Filter feature and the SUBTOTAL function together to get the sum value of rows. Follow the given steps.

  • Firstly, select any of the header cells.
  • Then, go to the Data tab and select Filter.

Sum Filtered Rows Using SUBTOTAL Function in Excel

  • Afterward, write the following formula in Cell C10 and press Enter.
=SUBTOTAL(9,C5:C9)
  • Finally, we will see the sum value of range C5:C9 in that cell.

Note: In the argument of the SUBTOTAL function we used 9 which indicates the SUM function.
  • In a similar way, we can get the sum value of Column D.
  • Now, select the filter icon in the header and deselect any number of the column.

  • Finally, we will see the adjusted result without the deselected value.

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


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

This time, we will combine the SUM, ROW and MOD functions of Excel to add every 2nd value from a range. Please follow the procedures.

  • Firstly, type the following formula in Cell B15 to get the sum value there.
=SUM(B5:B14*MOD(ROW(B5:B14),2))
  • Then, press Enter and we will see the sum value in Cell B15.

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

Note: In the formula, we can insert any other number (K) instead of 2 as the argument of the MOD function and we will see the sum of every K-th value from the range.

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


8. Apply SUM & LARGE Functions to Sum Larger Values

We can apply the SUM & LARGE functions together to get the sum of larger numbers. Let’s walk through the procedures.

  • Firstly, write the following formula in Cell B15 and press Enter.
=SUM(LARGE(B5:B14,{1,2,3,4,5}))
  • Thus, we will see the sum of 5 largest numbers from the range.

Note: We can change the number of the largest numbers by changing the array {1,2,3,4,5} with more or fewer numbers. For example, if we want to get 3 largest numbers then you can use {1,2,3} instead.

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


9. Sum Rows Having Errors in Excel

Sometimes there can be errors in the dataset range then we can’t use the SUM function. We can easily avoid the error using the IFERROR function. Let’s follow the procedures given below.

  • Firstly, write the following formula in Cell B15 as we want the sum value there.
=SUM(IFERROR(B5:B14,0))
  • Consecutively, press Enter.
  • Finally, we will see the sum value of the range B5:B14 avoiding the cells with errors.

Sum Rows Having Errors in Excel


Conclusion

Summation of rows is widely used while working in Excel. Here, we have shown 9 quick methods to sum rows in Excel. If you have any queries or suggestions, please let us know by commenting. Visit our ExelDemy Website for similar articles regarding Excel.


Related Readings

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo