To sum a whole column, use =SUM(A:A); for a row, =SUM(1:1). For several at once, =SUM(A:B) or non-adjacent ones with commas: =SUM(A:A,C:C).
The formula must sit outside the column it sums — put =SUM(A:A) anywhere in column A and Excel returns a circular reference warning. Whole-column references also pick up anything typed below your data later, including stray values, so a bounded range like =SUM(A2:A100) or an Excel Table’s Total Row is safer for anything you’ll maintain. Alt+= inserts a SUM over the range above the cursor automatically.
In Excel, you can use the SUM function to sum values from an entire column or row. In this tutorial, we will look at all the methods that we can use for this.
Sum an Entire Column
As I said, you can use the SUM function to sum values from a column, and the following are the steps you need to follow:
- First, enter “=SUM” in the cell where you want to calculate the sum.
- Next, enter an opening parenthesis.
- After that, refer to the column for which you want to calculate the sum.
- In the end, enter a closing parenthesis and hit Enter to get the result.
As you can see in the above example, we have used the SUM function and referred to column A to get the sum of all the values in the column. And if you want to sum up a row entirely, you need to refer to the row the way we have done in the following example.
=SUM(A:A)
The formula for row 1 is:
=SUM(1:1)
Don’t enter the formula in the same column (or row) that you are summing. For example, =SUM(A:A) in cell A20 refers to itself, and Excel shows a circular reference warning. Put the formula in another column, or use a range that stops above the formula, like =SUM(A1:A19). SUM also ignores text, so a header in A1 doesn’t cause any problem.
Sum Multiple Columns and Rows
There could be a situation where you need to sum values from multiple columns or rows. In that case, you can use the method we have used in the following example. For multiple columns (columns A and B):
=SUM(A:B)
And for multiple rows, like rows 1 and 2:
=SUM(1:2)
Sum Non-Adjacent Columns or Rows
Now, in the following example, you have the SUM function to sum multiple non-adjacent columns and rows. In the following example, you have the formula to sum columns A and C:
=SUM(A:A,C:C)
And in this example, you have used the formula to sum non-adjacent rows.
In the above example, we have referred to rows 1, 3, and 5, separated by commas. So, you can add as many columns or rows as you want, each separated by a comma.
=SUM(1:1,3:3,5:5)
Each reference counts once, so don’t list a column twice, or its values are added twice.
Important Point
Keep in mind that when you refer to an entire column or row, there’s always a chance of getting an incorrect sum. In the below example, you have values from cell A1 to A16, but you are referring to the entire column.
If you enter a value in any of the cells (even by mistake), the formula will consider that value.
If you have a value in a cell somewhere below A16, that value will also be included in the sum.
Sum a Column in an Excel Table
A safer way is to convert the data into an Excel table (Ctrl + T) and refer to the table column by its name. The sum then covers only the table rows, and it grows on its own when you add new rows. For a table named Table1 with a Sales column, the formula is:
=SUM(Table1[Sales])
Sum Only the Visible Cells of a Column
SUM adds all the values, even in rows that are hidden or filtered out. If you only want the visible values, use SUBTOTAL with 109:
=SUBTOTAL(109,A:A)
And if you only need to see the total without a formula, select the column and look at the Sum on the status bar at the bottom of the Excel window. You can also select the cell below your values and press Alt + = (AutoSum) to insert a SUM formula for the range above.
Frequently Asked Questions
Does SUM(A:A) slow down Excel?
Not with SUM. Excel only looks at the used part of the column, so a few full-column SUM formulas are fine. Heavy array formulas with full columns can be slow, though.
Why does my column sum show 0?
The values are most likely numbers stored as text, which SUM ignores. Convert them to numbers (for example, with the warning icon ➜ Convert to Number) and the sum updates.
How do I sum a column without the header?
If the header is text, =SUM(A:A) already skips it. If the header is a number, like a year, use a range that starts from row 2, like =SUM(A2:A1048576).
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.