How to Sum Entire Column in Excel (9 Easy Ways)

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.

Sum Entire Column with Excel SUM Function

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(

Sum Entire Column with Excel SUM Function

  • Thirdly, select the column you want to add and close the first bracket. We have selected column D here.

Sum Entire Column with Excel SUM Function

  • Finally, hit Enter to see the result.

Sum Entire Column with Excel SUM Function


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.

Sum Entire Column with Excel SUM Function

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

Sum Entire Column with Excel SUM Function

  • Finally, press Enter to see the result.

Sum Entire Column with Excel SUM Function

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))

Sum Entire Column with Excel SUM Function

Here, the formula sums different columns first and then adds the outputs.

  • In the end, press Enter and see the result.

Sum Entire Column with Excel SUM Function

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.

Use of AutoSum to Sum Entire Column

  • Next, go to the Formulas tab and select AutoSum.

Use of AutoSum to Sum Entire Column

  • After selecting AutoSum, the formula will appear in the cell.

Use of AutoSum to Sum Entire Column

  • Now, press Enter to see the result.

Use of AutoSum to Sum Entire Column

  • Finally, you can do it for the rest of the columns also.

Use of AutoSum to Sum Entire Column


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.

Do Sum in the Status Bar for Entire Column

  • Then, look at the bottom right corner. You will see the sum of the selected cells.

Do Sum in the Status Bar for Entire Column

  • Now, select all values of the three columns.

Do Sum in the Status Bar for Entire Column

  • After that, you will see the sum of the three columns in the status bar.

Do Sum in the Status Bar for Entire Column


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)

Insert of SUM Function Manually to Total Entire Column

  • Thirdly, press Enter to see the result.

Insert of SUM Function Manually to Total Entire Column

  • Finally, use the Fill Handle to see the results for the adjacent columns.

Insert of SUM Function Manually to Total Entire Column

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)

Excel SUBTOTAL Function to Sum up Whole Column

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

Sum up Whole Column with AGGREGATE Function

  • Thirdly, hit Enter to see the result.

Sum up Whole Column with AGGREGATE Function

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

Sum Entire Column Using Excel Table

  • Finally, after selecting Sum, the cell will show the sum of the entire column.

Sum Entire Column Using Excel Table

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.

Excel  VBA to Sum Entire Column Except Header

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)

Sum Entire Column with SUMIF Function

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.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo