How to sum and average in Excel — when AutoSum grabs only part of the range, blanks vs zeros, numbers stored as text, totals after a filter

Just select — the status bar shows Average, Count and Sum

When you don't need the total written in a cell, simply drag over the cells. The status bar at the bottom of the Excel window shows their average, count and sum right away. Selecting B3:B9 (Mon–Sun) on the practice file gave Average 14.4 · Count 6 · Sum 72.

Add the numbers by hand, though, and you get 12 + 15 + 18 + 20 + 25 + 0 = 90. The status bar says 72 — Thursday's 18 is missing. That cell's 18 sits at the left edge with a green triangle in its corner: it looks like a number but Excel holds it as text (Excel's error checking on this PC flagged it as a number stored as text). 'Count 6' counts non-empty cells including text, so it also differs from =COUNT (5).

Trap 1 — AutoSum grabs only part of the range

The quickest way to write a total is to click the cell right below the numbers and press Home > AutoSum (Σ) or Alt+=. Excel looks upward for numbers, outlines the suggested =SUM(…) range with a dashed border, and Enter puts it in.

On the practice file, clicking B10 right under the table and pressing AutoSum gave a suggestion of =SUM(B7:B9) — only Fri, Sat and Sun. Going up in search of numbers, Excel stopped at the text cell (Thursday). Press Enter as is and you get 45, with no warning.

So after pressing AutoSum, look at how far the dashed border reaches before you press Enter. If it's too short, drag over B3:B9 again while the formula is still being edited. Selecting the numbers and the total cell together before pressing AutoSum used exactly what you selected (on the test sheet, B2:B9 gave =SUM(B2:B8)) — but while Thursday is text that's still 72.

Trap 2 — numbers stored as text drop out of SUM

We added the same seven cells several ways (measured on the test sheet B2:B8; written here with the practice file's addresses). Functions (SUM, AVERAGE, COUNT) skip text inside a range, while chaining cells with + converts the text '18' and adds it. So one table gives two totals.

The fix is to turn Thursday into a real number. Click the cell, then the ⚠ button > Convert to Number — the value moves to the right and the green triangle disappears. After that =SUM(B3:B9) gave 90 and =AVERAGE(B3:B9) gave 15. With several text cells, select them all and convert them in one go.

Averages — blanks are left out, zeros count

=AVERAGE adds only cells that contain numbers and divides by how many there are. A blank cell like Wednesday (closed) is skipped entirely, while a 0 like Sunday counts as a day. On the fixed sheet =AVERAGE(B3:B9) was 90 ÷ 6 = 15.

Which average is right depends on what the sheet means. Write 0 for days you were open but sold nothing, leave closed days blank, and =AVERAGE becomes 'average of open days'. Typing 0 on a closed day pulls the average down. (The seven-day figure in the last row is calculated, not read from the sheet.)

Hidden rows and filters — SUM adds everything, SUBTOTAL only what's visible

Hiding the Saturday row (25) left =SUM at 90 — SUM includes hidden rows (measured on the test sheet with the same data). The function that adds only visible rows is SUBTOTAL: a first argument of 109 means 'sum, skipping hidden rows', and 9 means 'sum, including rows you hid by hand'.

Rows hidden by a filter are skipped by both 9 and 109. Filtering Sold to 15 or more left four rows — Tue, Thu, Fri, Sat (the status bar reported 4 of 7 records) — and SUBTOTAL gave 78 while SUM stayed at 90.

Common snags

• The total is lower than your hand count: a number stored as text (left-aligned, green triangle) — ⚠ > Convert to Number
• AutoSum added only some cells: it stops at a blank or text cell above — check the dashed range before Enter and drag again
• The average is too low: closed days were typed as 0 — leave them blank or use =AVERAGEIF(range,">0")
• Hidden rows are still added: =SUM adds everything — use =SUBTOTAL(109, range)
• Status bar Count ≠ COUNT: the status bar counts non-empty cells (text too); COUNT counts numbers only

Practice

Open the practice file (a made-up week of sales) and try these steps.

1. 1Select B3:B9 and read Average, Count and Sum on the status bar (14.4 · 6 · 72).
2. 2Click B10 and press AutoSum — see how far the dashed range goes, then press Esc.
3. 3Convert Thursday (B6) with ⚠ > Convert to Number, then enter =SUM(B3:B9) in B11, =AVERAGE(B3:B9) in B12 and =AVERAGEIF(B3:B9,">0") in B13.
4. 4Put =SUBTOTAL(109,B3:B9) in B14, filter Sold to 15 or more, and compare B11 with B14.

The answer file has Thursday fixed to a number and shows Total 90 · Average 15 · days-with-sales average 18 · visible-rows total · 6 number cells · 6 non-empty cells, with the filter on.

FAQ