Excel AutoSum selecting the wrong range

How AutoSum chooses a range

Click the cell under a column and press Alt+= (Cmd+Shift+T on a Mac), or use Home > AutoSum. Excel looks up from that cell and selects the unbroken block of numbers it finds. If there are no numbers above, it looks to the left instead.

It is a guess, and Excel shows it as a moving border before you confirm. Most wrong totals come from pressing Enter without looking at that border.

1. Select B9, the empty cell under the numbers.
2. Press Alt+= (Windows) or Cmd+Shift+T (Mac).
3. Look at the dashed border: it should cover B2:B8.
4. If it does not, drag over B2:B8 with the mouse, then press Enter.

Problem 1: a blank row stops the selection

Row 6 is empty, so AutoSum in B9 stops at the gap and selects only Week 5 and Week 6. The total looks plausible, which is why this one slips through.

Fix it by selecting the whole range before you press Alt+=, or by editing the formula. Better still, remove blank rows inside data, or turn the data into a table with Ctrl+T and use its Total Row, which always covers every row.

Problem 2: numbers stored as text are ignored

Even with the full range, Week 3's 990 is text (it sits on the left of the cell and may show a green triangle). SUM skips text, so the total is 990 short with no warning.

Compare COUNT with COUNTA over the range: COUNT counts numbers only, COUNTA counts anything that is not empty. If they differ, something is text. Convert it with the warning icon > Convert to Number.

A total that counts text numbers too

While you fix the data, the double minus forces text numbers into real numbers inside the formula. SUMPRODUCT then adds them; the blank row counts as 0.

Treat this as a stopgap. Converting the cells properly also fixes sorting, charts and every other formula that reads them.

Problem 3: AutoSum sums the row instead of the column

If the cells directly above the active cell are empty, AutoSum looks left and totals the row. This happens when you put a total in a cell that is one row too low, or in a column that starts below a gap.

Check which way the border runs. Click in the formula bar and correct the range, or select the column of numbers first and then press Alt+=, which puts the total right under your selection.

Problem 4: subtotals and filtered rows

If the column already contains a subtotal row, a SUM over the whole column counts those numbers twice. Use SUBTOTAL(9, range) for both the subtotals and the grand total: SUBTOTAL ignores other SUBTOTAL cells inside its range.

On a filtered list, AutoSum inserts SUBTOTAL(9, ...) automatically, which adds only the visible rows. A plain SUM keeps adding the hidden rows too, so do not change it back to SUM if you want the visible total.