How to Find the Sum of a Column in Excel With a Formula

To find the sum of a column in Excel, enter a SUM formula such as =SUM(A2:A20) in an empty cell; if your data has no gaps, you can also select the cell below it and choose Home > AutoSum. Both methods add the numbers in your chosen range and update the total when those numbers change. Before you start, identify which cells contain the values and where you want the result to appear.

What You’ll Need

An Excel workbook with the column of values you want to total. The values should be numbers, not numbers stored as text.
An empty cell for the total. If you plan to use AutoSum below the data, leave that cell clear.
The range of cells to add, such as A2:A20. A range can include blank cells; SUM ignores them.

Step-by-Step

Find the values you want to include. Look at the column and note its first and last data cells. For example, if the heading is in A1 and the values run from A2 through A20, the range is A2:A20. Excluding the heading helps keep the formula clear, even though SUM generally ignores text in a referenced range. Decide whether you want every value or only a selected section of the column.
Choose an empty result cell. Click a blank cell where the total should appear. You can place it below the list, beside it, or on a summary sheet. If you plan to add more rows later, leave room for them or choose a range that includes the likely new rows. Do not put a total inside the range you are adding: doing so can make the formula include itself and produce a circular reference.
Enter a SUM formula. With values in A2:A20 and the result in a cell outside that range, type =SUM(A2:A20) and press Enter. Excel adds the numbers in the specified cells and displays the result in the formula cell. In this example, if A2:A20 contains 5, 12, and 8 with the rest blank, the result is 25. Use your own column letter and row numbers in place of A2:A20.
Check that Excel has accepted the formula. After pressing Enter, the cell should show a number rather than the formula text. Click the result cell to see the formula in the formula bar. Check that the first and last row references match the data you meant to include. If you see an error or the formula as text, see Troubleshooting below.
Use AutoSum for a quick total. If the values are in A2:A20, click the empty cell immediately below them, A21. On the Windows ribbon, select Home > Editing > AutoSum, then press Enter if needed to accept the suggested range. Excel usually detects the adjacent run of numbers and inserts a SUM formula. Review the outlined range before accepting it, especially if there are blank rows or other numbers nearby.
Use the keyboard shortcut if you prefer. With the blank cell below the numbers selected, press Alt+= on Windows to insert AutoSum. In Excel for Mac, the equivalent shortcut is Command+Shift+T. Excel proposes a range; verify it and press Return to accept. If the suggested range is wrong, cancel or edit the formula before confirming rather than assuming the shortcut selected exactly what you intended.
Confirm the result with a simple check. Compare the total with a few values you can add by hand, or temporarily select the data range and look at Excel’s status bar for Sum. If the status bar is not showing a sum, right-click it and enable Sum. This check is useful for spotting an omitted row, a mistaken range, or a value that Excel is treating as text.

The example uses a fixed range, which is often the clearest option for a short list. If you want to include every numeric cell in column A, and your result is in a different column or on another sheet, use =SUM(A:A). This adds all numbers in column A, including numbers entered farther down later. Do not put that formula in column A itself: including the result cell in the whole-column reference can create a circular reference.

Common Mistakes

Including the result cell in the range: A formula in A21 that adds A2:A21 refers to itself. This can trigger a circular reference warning or a problematic result. If the total is in A21, sum A2:A20 instead. When using a whole-column reference such as A:A, put the total in another column or on a separate sheet.

Using the wrong starting or ending row: A formula can calculate correctly while leaving out the first or last value. Check the range displayed in the formula bar, and compare its endpoints with the actual data. Watch for headings, subtotals, and gaps that might confuse AutoSum’s guess.

Assuming text that looks like a number will be added: Values imported from a website or another system may be stored as text. SUM ignores text in a referenced range, so the displayed total can be too low. A small green triangle or left-aligned numbers can be clues, though formatting and alignment can vary.

Overwriting the formula with a fixed number: If you type a total directly into the result cell, it will not recalculate when the source values change. Use a formula when you need a total that stays current, and avoid pasting over the formula cell later.

Tips

Use clear labels and formatting. Add a nearby label such as Total so it is obvious what the result represents. You can format the result as currency, a percentage, or a number from Home > Number, as appropriate for the data. Formatting changes how the answer looks, not the values included in the sum.

Make room for new entries. If you expect to add data, you can sum a larger range than the current list, provided the result cell is outside it. For example, with current values in A2:A20 and the total in B2, the formula =SUM(A2:A100) includes future entries through row 100. It returns 25 if the only numbers currently in that range are 5, 12, and 8. A whole-column formula can also accommodate additions, but keep the result outside the summed column.

Consider an Excel table for growing lists. Select your data and choose Insert > Table to make a table; check that the header option matches your layout. Tables expand when you add rows directly below them and make it easier to manage changing lists. You can turn on a total row from Table Design > Total Row when the table is selected, then choose Sum in the total cell’s drop-down. The Table Design tab appears when a table cell is selected.

Know what the ordinary SUM includes. SUM adds numeric values in its range, skips blanks and text, and includes rows that are hidden or filtered out. If you need a total that changes when a filter hides rows, use a subtotal formula instead and choose the behavior that fits your sheet.

Troubleshooting

The cell shows the formula instead of the answer. The cell may be formatted as Text, or the formula may have been entered with an apostrophe before the equals sign. Change the cell format to General using Home > Number, then edit the cell and press Enter to re-enter the formula. If Show Formulas is enabled and formulas are visible throughout the sheet, press Ctrl+` on Windows to switch back to results. On a Mac, use Control+`; the key may be located differently depending on the keyboard layout.

The total is zero or lower than expected. Check whether the cells contain numbers stored as text. Select a suspicious cell and look for a warning icon that offers Convert to Number. If no warning appears, try changing the cell format to General and re-entering the value, or use Data > Text to Columns and complete the prompts to convert a column of numeric text. Then check the SUM range again; conversion alone will not fix an omitted row.

AutoSum selected the wrong cells. Click the formula cell and inspect the highlighted range, or read the reference in the formula bar. Edit the start and end references to match your data before pressing Enter. AutoSum can stop at a blank row or include a nearby subtotal, so manually selecting the range is often safer when the list has gaps or extra figures alongside it.

Excel reports a circular reference. The formula likely includes its own cell, either directly or through another formula. Move the result outside the input range or change the range so it ends at the last data row. If the warning remains, inspect formulas that depend on the total cell as well; a circular reference can involve more than one cell.

The result does not change after editing a value. Press F9 to recalculate the workbook, then check Formulas > Calculation Options. If calculation is set to Manual, switch it to Automatic. Also confirm that the edited value is inside the formula’s range and that the result cell still contains a formula rather than a typed number.

Frequently Asked Questions

Can I sum a whole column in Excel?
Yes. With the result outside column A, use =SUM(A:A) to add numeric values in that column. A whole-column reference includes future entries, but it also includes any other numbers in the column, so use a limited range if the column contains unrelated figures.

Can I add a column without typing a formula?
Yes. Select the blank cell below a continuous list of numbers and choose Home > Editing > AutoSum. Review the proposed range and accept it. AutoSum inserts a SUM formula for you, so the result can update when the source values change.

Does SUM include blank cells or text?
No. SUM skips blank cells and text values in a referenced range, and adds the numeric values it finds. A number stored as text is not treated as a numeric value, which can explain a total that is lower than expected.

How do I total only the rows left visible by a filter?
With the values in A2:A100 and the result outside that range, use =SUBTOTAL(109,A2:A100). It sums visible values and excludes rows hidden by a filter or hidden manually. The function number 9 also ignores filtered-out rows but includes manually hidden rows.

Will the total update when I change a value?
Yes, if the result cell contains a formula and Excel is set to calculate automatically. SUM recalculates when a value within its referenced range changes. If new data is added beyond a fixed range, edit the range or use a suitably larger range or table.

Why does my sum seem incorrect when some rows are hidden?
Ordinary SUM includes hidden and filtered-out rows. If you want only visible rows, use SUBTOTAL as described above. If you do want every row included, check that the formula range includes the hidden rows and that the cells contain numeric values.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.