Check a spreadsheet total without trusting one formula

First decide what belongs in the total

Before editing, preserve a separate working copy through File > Make a copy if your permissions allow it. Name it clearly as a checking copy. If copying is restricted, do the read-only selection checks and ask the owner to investigate changes. Avoid troubleshooting by sorting or deleting rows in a shared original.

Write a one-sentence rule: for example, “October invoice amounts, including credits, excluding the previous balance.” Identify the amount column, first data row, last data row, and total cell. Check dates and invoice identifiers against the source list. A correctly selected range cannot expose an invoice that never entered the sheet.

Use this hypothetical example throughout. Column A contains invoice identifiers; B2:B7 contains six amounts in the same currency. B8 is the existing total. These figures are an exercise, not results from a tested account.

The expected total is 500.00. The first three amounts make 250.00; the last three also make 250.00. This grouping gives you a simple independent arithmetic check using paper or a calculator.

Build a fresh selection without copying the formula

1. Click B8 and read the formula bar. Record the expression before changing anything. A formula ending at B6 misses the last entry; an unexplained typed number has no live calculation to inspect.
2. Click B2, then drag through B7. Confirm the highlighted boundary covers exactly the six amounts. Keep B8 outside the selection. Selecting the whole column could include the total itself, older records, or intermediate totals.
3. Look at the bottom-right calculation summary. Click it and select Sum if another calculation is active. With all six example amounts stored numerically, the result should be 500.00, possibly displayed without trailing zeros.
4. Compare that result with B8. Next select B2:B4 and B5:B7 separately. Each should sum to 250.00. Read the source amounts into a calculator in those groups and compare again.

Google documents the summary controls in See the sum & average, including Count and Count numbers. It also warns that the feature does not work for some number or currency formats. A missing summary is therefore a limitation to investigate, not evidence that the range totals zero.

If B8 shows 480.00 while the fresh selection shows 500.00, inspect whether the formula stops at B6. Correct the endpoint only after confirming that B7 belongs under your inclusion rule. If both show 480.00, the problem may be an ignored value rather than a missing endpoint. Matching totals alone have not cleared the sheet.

Make six rows prove they contain six numbers

Keep B2:B7 selected, click the bottom-right summary, and choose Count numbers. Expect six for this example. A count of five means at least one of the six cells is not supplying a numeric value; inspect for text, a blank, or an error. Count by itself is a different check and should not substitute for Count numbers.

A suspicious entry can look exactly like an ordinary amount. On the checking copy, choose an unused cell outside the invoice table and enter =ISNUMBER(B7). TRUE means B7 contains a number; FALSE means it does not. Google’s ISNUMBER documentation explicitly distinguishes a numeric value from digits held in a text string. Repeat against the suspected cells rather than overwriting them.

Suppose B7 contains text representing 20.00. The numeric count can be five and the sum 480.00 even though six amounts appear on screen. Compare B7 with the original invoice. For a single confirmed mistake, re-enter the amount as a number on the copy, then repeat both Count numbers and Sum. Changing its visual currency format alone is not proof of conversion.

For imported text, try =VALUE(B7) in another unused cell. Google’s VALUE reference says conversion works for recognized number, date, and time strings, and fails with an error for unrecognized strings. Check the converted amount against the source before using it. A date-like string can become a date serial number; a successful conversion is not automatically the intended money value.

Do not strip every comma or period from an imported column. Those characters can mean different things in different number conventions. If one example converts incorrectly, stop and establish the export’s decimal convention before attempting a bulk repair.

Resolve the disagreement caused by hidden rows

Look for skipped row numbers and active filters. Decide whether your target is all six invoices or only a visible subset. For the all-invoice check, work on the copy with the relevant rows revealed and filtering removed. Do not assume a selection summary and an existing formula apply identical hidden-row rules.

For an explicitly visible-only total, enter =SUBTOTAL(109,B2:B7) in an empty cell outside the data. Google’s SUBTOTAL documentation defines 9 as the sum operation and the 109 form as skipping hidden values. Filtered-out rows are excluded with either 9 or 109; manually hidden rows are the reason to choose 109 here.

In the example, hiding B5’s row leaves a visible-only total of 300.00, while the six-invoice total remains 500.00. Both figures can be useful, but label them with their scope. SUBTOTAL also ignores nested SUBTOTAL calls. It does not magically recognize a manually typed subtotal: identify detail rows before adding them.

Finish with a reconciliation you can explain

When the figures disagree, subtract the smaller from the larger and look for that difference among the source entries. A 20.00 gap suggests checking the final example invoice; a 200.00 gap suggests checking the hidden row. These are leads, not diagnoses: two unrelated errors can cancel, and duplicated invoices can have different amounts.

Check invoice identifiers against the external record as well as the amounts. Six numeric cells and a total of 500.00 cannot prove that the correct six invoices were entered. Confirm credits carry the intended sign, exclude summary rows, and keep different currencies separate unless a documented conversion belongs in the calculation.

Record the checked range, inclusion rule, numeric count, fresh sum, outside-record comparison, and each correction. Remove only your temporary helper cells after preserving the findings. Undo an accidental edit promptly; if several changes are uncertain, return to the untouched original and repeat on a new copy. Apply confirmed corrections to the working original deliberately, then rerun the checks there before sharing its total.