How to Sum a Column in Excel: SUM and AutoSum
· 6 min read
To sum a column, click the cell under the last number and type =SUM(, then the first cell, a colon and the last cell, and press Enter. Or click AutoSum and let Excel suggest the range for you:
=SUM(C2:C8)That adds every cell from C2 down to C8. The formula updates by itself when any of those numbers change, which is why you should never type a total in by hand. The rest of this guide covers AutoSum, summing a whole column, and the reasons a SUM can return the wrong total.
The example table
Units sold per product in one week. The total is in C9, and column F has the other SUM formulas from this guide.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Units sold | Formula | Result | |
| 2 | Copy paper | Office | 340 | Three separate cells | 845 | |
| 3 | Ballpoint pens | Office | 515 | AI's formula: stops a row early | 1,710 | |
| 4 | Coffee pods | Kitchen | 280 | Fixed: first to last data row | 1,915 | |
| 5 | Paper plates | Kitchen | 190 | |||
| 6 | Sponges | Cleaning | 225 | |||
| 7 | Bleach | Cleaning | 160 | |||
| 8 | Hand soap | Cleaning | 205 | |||
| 9 | Total | 1,915 |
Sum formula in Excel: step by step
- Click C9, the empty cell under the numbers.
- Type
=SUM(. - Drag from C2 to C8 with the mouse, or type
C2:C8. - Type
)and press Enter.
Result: 1,915. The colon means "everything from C2 to C8", so all seven rows are included.
SUM also accepts separate cells and ranges, separated by commas. =SUM(C2, C4, C6) adds only those three cells and returns 845 (F2 in the sheet). You rarely need this for a column total, but it is useful for adding a few subtotals together.
AutoSum: the one-click version
AutoSum writes the SUM for you.
- Click C9.
- On the Home tab, click AutoSum (the Σ symbol, near the right end of the ribbon). It is also on the Formulas tab.
- Excel draws a moving border around the range it thinks you want and shows
=SUM(C2:C8)in the cell. - If the range is right, press Enter. If not, drag over the correct cells first.
On Windows, the keyboard shortcut is Alt+=. Select the cell under the column, press Alt and the equals sign together, then Enter.
AutoSum guesses by looking up from the selected cell until it reaches a gap. Always look at the border before you press Enter. If there is an empty row or a text cell in the middle of your numbers, AutoSum may stop there and leave the top rows out.
The small arrow next to AutoSum also offers Average, Count Numbers, Max and Min, which build those formulas the same way.
Quick total without a formula
Select C2:C8 and look at the status bar at the bottom of the window. Excel shows the Sum, Average and Count of the selection. It is a fast way to check a number. It does not put anything in the sheet, so it is a check, not a total.
Sum a whole column
To add every number in column C, however many rows it grows to:
=SUM(C:C)This picks up rows you add later without editing the formula. Two cautions:
- Do not put it inside column C. A total in C9 that sums C:C includes itself. Excel shows a circular reference warning and the result is 0 or wrong. Put the whole-column total in another column.
- Headers and labels are skipped, because SUM ignores text. But any other number in the column, such as a subtotal row or a year typed in a note, is added too.
A safer way to keep a total growing with the data is to format the list as a table (Insert > Table) and turn on the Total Row on the Table Design tab.
Sum with a condition
If you only want some rows, such as units for the Cleaning category or revenue for completed orders, SUM is the wrong tool. Use SUMIF for one condition or SUMIFS for several: see SUMIF with multiple criteria.
Excel SUM not working: what to check
The total is too low
- The range stops early.
=SUM(C2:C7)on our table returns 1,710, leaving out Hand soap. Click the total cell and read the range: it must end on the last data row. - Numbers stored as text. If Coffee pods' 280 was pasted in as text, SUM skips it and returns 1,635. Text numbers are usually left aligned and may show a small green triangle in the corner. Select the cells, click the warning icon and choose Convert to Number.
- Rows added below the range. A new product typed under the total is not part of C2:C8. Insert new rows inside the range, or use a table so the total extends by itself.
The total does not change
- Calculation is set to manual. Go to Formulas > Calculation Options and choose Automatic. On Windows, F9 recalculates once.
- The formula shows as text. If the cell displays
=SUM(C2:C8)instead of a number, the cell was formatted as Text before you typed. Change the format to General, then click into the cell and press Enter again.
The total is too high or odd
- Hidden or filtered rows. SUM includes them. To add only the visible rows, use
=SUBTOTAL(109, C2:C8), which ignores both filtered and manually hidden rows. - A subtotal inside the range. If one row in the middle is already a total, SUM counts those numbers twice.
- Displayed numbers are rounded. A column of prices may show two decimals while storing more, so the total can differ by a cent from adding the visible values. See round to 2 decimal places.
Common mistakes
Trusting an AI-written total without reading its range. AI assistants regularly hand back a SUM whose range stops one row short of the data, or starts on the header row of a different block. The formula has no error and the number looks plausible, as with the 1,710 above. Click F3 in the sheet and then F4: the blue range in F3 stops at Bleach. Click the total and read the range in the formula bar. Does it start on the first data row and end on the last one? If you are checking a bigger sheet, Trace Precedents draws the range for you: see how to trace precedents.
Typing the total by hand. It is correct today and wrong the moment a number changes.
Using + for long lists. =C2+C3+C4+C5+C6+C7+C8 works, but it is easy to skip a cell, and it breaks with #VALUE! if a cell contains text such as "n/a". SUM simply ignores text.
Summing a column that includes its own total. This gives a circular reference. Keep the total outside the range it adds.
FAQ
How do I sum a column in Excel quickly?
Click the cell below the numbers and press Alt+= on Windows, or click AutoSum on the Home tab, then press Enter.
How do I sum an entire column?
Use =SUM(C:C) in a cell outside column C. It adds every number in the column, including rows you add later.
Why is my SUM formula returning 0?
Usually the numbers are stored as text, or the formula refers to itself (a circular reference). Check the alignment of the numbers and the warning in the status bar.
Why does my Excel SUM not update?
Calculation is probably set to Manual. Switch it back under Formulas > Calculation Options > Automatic.
How do I sum only visible cells?
Use =SUBTOTAL(109, C2:C8). Unlike SUM, it skips rows hidden by a filter or by hand.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Add up a whole column” →