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.
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.
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.
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.
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.
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.
- Finally, we will see the sum value of cells above Cell B10.
Read More: Sum Formula Shortcuts in Excel (3 Quick Ways)
Similar Readings
- How to Sum Colored Cells in Excel (4 Ways)
- Sum If a Cell Contains Text in Excel (6 Suitable Formulas)
- How to Sum Cells with Text and Numbers in Excel (2 Easy Ways)
- Excel Sum If a Cell Contains Criteria (5 Examples)
- How to Use VLOOKUP with SUM Function in Excel (6 Methods)
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.
- Consecutively, a Create Table window will appear. Just, hit OK.
- Afterward, select Cell D10 to get the sum value there. Right–click 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.
- 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.
- 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.
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.
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.
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.