Mursalin Ibne Salehin
Summing up is an important aspect of any datasheet in Excel. Sometimes, we need to sum the entire column in excel for effective data analysis. There are various ways to sum the entire column in Excel. Today, we are going to learn these various methods in Excel.
Download Practice Book
Download the practice book.
9 Quick Ways to Sum Entire Column in Excel
1. Sum Entire Column with Excel SUM Function
We will use the SUM Function in this method. For this purpose, we will use a dataset of four columns. These columns have different employees’ sales information of January, February & March months. We will use the same dataset in all methods.
1.1 For Single Column
We will calculate the sum of a single column in this sub-method.
STEPS:
- Firstly, select any cell. We have selected Cell C11 here.
- Secondly, type the formula.
=SUM(
- Thirdly, select the column you want to add and close the first bracket. We have selected column D here.
- Finally, hit Enter to see the result.
1.2 For Multiple Columns
We can also use the SUM Function to sum multiple columns.
STEPS:
- In the beginning, select any cell. We have selected Cell C11 here.
- Secondly, type the formula.
=SUM(- Then, select the columns you want to add and close the first bracket. We have selected Columns D & E here.
- Finally, press Enter to see the result.
Cell C11 is showing the summation of Columns D & E.
1.3 For Non-Contiguous Columns
Sometimes, we need to sum non-contiguous columns. We can sum non-contiguous columns with the SUM Function.
STEPS:
- First, select Cell D11.
- Next, put the formula.
=SUM(SUM(C:C),SUM(E:E))
Here, the formula sums different columns first and then adds the outputs.
- In the end, press Enter and see the result.
Read More: Sum Multiple Columns Based on Multiple Criteria in Excel
2. Use of AutoSum to Sum Entire Column
The AutoSum feature of excel also helps us to sum the entire column. Follow the steps below.
STEPS:
- Firstly, select a cell below the column where you want to display the sum. We have selected Cell D10.
- Next, go to the Formulas tab and select AutoSum.
- After selecting AutoSum, the formula will appear in the cell.
- Now, press Enter to see the result.
- Finally, you can do it for the rest of the columns also.
3. Find Sum in the Status Bar for Entire Column
Status Bar of Excel is capable of showing the sum of the selected cells. Follow the steps below for this method.
STEPS:
- First, select the range of cells for your desired column.
- Then, look at the bottom right corner. You will see the sum of the selected cells.
- Now, select all values of the three columns.
- After that, you will see the sum of the three columns in the status bar.
4. Insert of SUM Function Manually to Total
We can insert the SUM Function manually to sum the entire column. Steps are given below:
STEPS:
- Firstly, select Cell C10.
- Secondly, type the formula.
=SUM(C5:C9)
- Thirdly, press Enter to see the result.
- Finally, use the Fill Handle to see the results for the adjacent columns.
Read More: How to Total a Column in Excel (7 Effective Methods)
5. Excel SUBTOTAL Function to Sum up Whole Column
The SUBTOTAL Function also helps us to total an entire column. It is mostly the same as the previous method.
STEPS:
- Select Cell C10 at first.
- After that, type the formula.
=SUBTOTAL(9,C5:C9)
- Then, press Enter to see the result.
- In the end, use the Fill Handle to see the results for the adjacent columns.
6. Sum up Whole Column with AGGREGATE Function
We can use the AGGREGATE Function to sum an entire column. It has a great advantage. If we hide any cell, the sum will be adjusted accordingly. To know about this method, follow the steps below.
STEPS:
- Firstly, select Cell C10.
- Secondly, type the formula.
=AGGREGATE(9,5,C5:C9)
- Thirdly, hit Enter to see the result.
- Finally, use the Fill handle for the rest of the adjacent cells and see the result.
- Now, the most interesting fact, if we hide Row 6, the values will be adjusted in the last row.
Read More: How to Add Up Columns in Excel (12 Methods)
7. Sum Entire Column Using Excel Table
Excel Table has a great advantage when we need to sum an entire column. In this method, we will show how we can use the Excel Table to sum columns.
STEPS:
- First, select any cell of your dataset.
- Next, go to the Insert ribbon and select Table.
- Now go to the Table Design tab and select Total Row.
- Then the dataset will show an extra row for displaying the sum.
- After that, select Cell D10 and select Sum from the drop-down menu.
- Finally, after selecting Sum, the cell will show the sum of the entire column.
Read More: How to Sum Columns in Excel Table (7 Methods)
8. VBA to Sum Entire Column Except Header
We can use the VBA feature of excel to sum the entire column except the header. For this purpose, we need to type some code in the Visual Basic window.
Follow the steps below.
STEPS:
- Firstly, go to the Developer ribbon and select Visual Basic.
- Secondly, type the code in the Visual Basic Window and press the F5 key to Run the code.
Public Function SumEntire(x As Range) As Double
Dim xColIndex As Integer
Dim xRowIndex As Integer
Dim wks As Worksheet
Set x = x.Columns(1)
Set wks = x.Parent
xIndex = x.Column
xRowIndex = wks.Cells(Rows.Count, xIndex).End(xlUp).Row
Set pRng = wks.Range(wks.Cells(2, xIndex), wks.Cells(xRowIndex, xIndex))
SumEntire = Application.WorksheetFunction.Sum(x)
End Function
- Thirdly, select Cell C11 and type the formula.
=SUMENTIRE(E:E)
- Finally, hit Enter to see the result.
Read More: Sum Every nth Column in Excel(Formula and VBA Code)
9. Add Entire Column with SUMIF Function
Sometimes, we need to sum a column with some criteria. In those cases, we can use SUMIF Function. Here, we will use only the first two columns from our previous dataset.
Follow the steps to know more about the method.
STEPS:
- Firstly, select Cell C10.
- Secondly, type the formula.
=SUMIF(C5:C9.”>1200,C5:C9)
Here, the SUMIF Function will add values over 1200.
- Finally, press Enter to see the result.
Some Common Errors to Sum Entire Column in Excel
Some errors can occur when we try to calculate a sum. We should be extra attentive to avoid the error. The errors are listed below.
- #NAME? – Check formula spelling to avoid this.
- #REF! – It occurs when previously referenced columns or rows do not exist anymore.
- #VALUE! – It occurs if we have non-integers in the Sum Formula.
Conclusion
Summing data is necessary for the analysis of our dataset. Sometimes, we need to sum up an entire column quickly in Excel. We have discussed some effective ways to calculate the total sum of an entire column in Excel. Hope these methods will help you to find solutions to your problem. Last of all, if you have any queries or suggestions, feel free to comment below.
