SUM: add the right numbers, not just more numbers

SUM is simple until the range contains totals, subtotals, text or an extra row that should not be counted. This page starts with the range you mean to include, then looks at how the result can go wrong.

Formula syntax

=SUM(number1, [number2], ...)

What goes into it

A quick example first

=SUM(12,8,5)

=SUM(B2:B12)

Reconcile a small expense list

Use A1:B5 for this sample. Enter the apostrophe before 75 to deliberately store that amount as text; it will not normally display in the cell.

A total can look reasonable while missing an imported text value. SUM adds 120 + 80 – 20, and ignores the referenced text in B5. Changing the number format alone does not always convert stored text. Inspect the source and convert deliberately before reconciling the total.

Before you copy this into a real sheet

Place the total outside B2:B5 so it does not include itself. Ordinary SUM includes manually hidden and filtered-out rows; use SUBTOTAL for visible-row totals. An error in the input range is not the same as text and can propagate to the result. Keep refunds negative only if that sign convention matches the rest of the workbook. Expanding the list also requires expanding the range or using a structured table reference.

Useful habits

• Prefer ranges instead of long chains such as B2+B3+B4.
• SUM ignores text in referenced cells but typed text arguments can behave differently.
• Use SUBTOTAL when filtered-list behavior matters.

Where people get caught out

• Assuming hidden or filtered rows are automatically excluded.
• Including a total cell inside the range being totaled and creating a circular reference.

Other functions worth a look

A detail worth remembering

If the number surprises you, select a few source cells and total them manually. A misplaced grand-total row is a more common explanation than a problem with SUM itself.