Exceljet

Quick, clean, and to the point

Sum entire column

Excel formula: Sum entire column
Generic formula 
=SUM(A:A)
Summary 

To sum an entire column without providing a specific range, you can use the SUM function with a full column reference. In the example shown, the formula in F5 is:

=SUM(D:D)

The result is the sum of all numbers in column D. As data is added to the table, the formula will continue to return a correct total.

Explanation 

In this example, the goal is to return the total for an entire column in an Excel worksheet. One way to do this is to use a full column reference.

Full column references

Excel supports "full column" like this:

=SUM(A:A) // sum all of column A
=SUM(C:C) // sum all of column C
=SUM(A:C) // sum all of columns A:C

You can see how this works yourself by typing A:A or C:C into the name box (left of the formula bar) and hitting return. You will see Excel select the entire column.

Example

To solve the problem in the example worksheet, we can use a full column reference to column D with the SUM function like this:

=SUM(D:D)

The result is the sum of all numeric values in column D. One advantage to full column references is that they will automatically include new data. As entries are added to the table, the formula will automatically include the new amounts.

Advantages and risks

The main advantage to full column references is simplicity. Simple and very compact, a full column reference will automatically include all data in a column, even as data is added or removed. However, full column references come with certain risks. One risk is that you may accidentally include extra data in a calculation. For example, if you use =SUM(A:A) to sum numbers in column A, you are targeting over 1 million cells. If column A includes forgotten dates somewhere far below in the worksheet, the numeric value of these dates will be included, and SUM will return an incorrect result. Another risk is performance. Because so many cells are included, full column references can cause severe performance problems in certain worksheets or formulas.

Alternatives

There are good alternatives to full column references. If you need to target data that may change in size, a good solution is an Excel Table, which will automatically adapt to changing data. Another option is to use a dynamic named range.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.