An example
=SUM(B2:B50)
The figures in column B run on to row 73. Rows 51 to 73 are typed, formatted and visible, and none of them is in the total.
How it happens
Somebody writes the total when the list is 49 rows long. Later rows are added below the old last row rather than inside the range, so the range never grows. Excel and Google Sheets only stretch a range by themselves when rows are inserted inside it.
The same thing happens along a row: twelve months summed as eleven, because the last month was added after the total was written.
Why it matters
The total still looks reasonable. It is simply smaller than it should be, and the rows it misses are the ones nobody thinks to check: the most recent.
It is the mistake behind one of the best known spreadsheet errors in economics: in 2013 an influential paper by Reinhart and Rogoff was found to have averaged a range that left out five countries.
How to find it by hand
Click the total and read the range in the formula bar. Then click the top of the column and press Ctrl and the down arrow (Cmd on a Mac) to jump to the last filled cell. If the two row numbers disagree, the total stops short.
Do the same for every total, average, count and lookup in the file, along rows as well as down columns.
How Proper Spreadsheets finds it
The checker compares every range with the block of data it sits in and reports any range that ends before the data does, with the cell, the range it covers and the rows it leaves out. Totals, averages, counts and lookups are all checked, down columns and along rows.
Check a whole file in seconds
Finding this by hand means reading every formula in the file. The free check reads them all, along with six other mistakes that show no error, and points to each cell. Your file is deleted as soon as the check finishes.
Other mistakes worth checking for
- A number typed into a formula: It is right until the rate changes. Then it is wrong in every row it was copied into.
- One row that calculates something different: Every row in the column does the same thing, except one. The answer it shows looks perfectly normal.
- A figure typed over a formula: The column calculates, row after row, until the one row where somebody typed the answer in.
- A total that disagrees with its rows: The rows add up to one figure and the total says another.
- Error values, and the one cell causing them: Two hundred cells show #REF!. One of them is the cause, and it is rarely the first one you see.
- A formula that points at a sheet that is gone: The cell shows 0. It should show an error, and would, if IFERROR were not hiding it.