Most Excel totals come from four functions: SUM, SUMIF, SUMIFS and SUBTOTAL. SUM adds a range, SUMIF adds the rows that meet one condition, SUMIFS adds the rows that meet several conditions, and SUBTOTAL respects filters. Knowing which one fits the job prevents most of the wrong totals people run into.
Pick the function by the job
SUM is the right tool when every number in a range should count. =SUM(D2:D100) covers the whole block and skips text and blank cells instead of failing. If the log keeps growing, turn it into a table with Ctrl+T so that =SUM(Table1[Amount]) expands as rows are added.
SUMIF takes one condition, and its arguments run criteria range first, then criteria, then the sum range: =SUMIF(B2:B100,”East”,D2:D100). SUMIFS flips that order and puts the sum range first. Many people use SUMIFS for everything so there is only one order to remember.
- SUM: add a whole range, text ignored
- SUMIF: one condition, sum range last
- SUMIFS: every condition must match (AND), sum range first
- SUBTOTAL 109: visible rows only, after a filter
Filters are the trap here. SUM still counts rows that a filter has hidden, so a footer built with SUM can disagree with what is on screen. SUBTOTAL leaves those rows out.
Criteria that go beyond ”equals”
Comparison operators go inside the quotes. ”>=1000” adds every amount of 1,000 or more. When the threshold lives in a cell, join it with an ampersand, as in ”>”&F1. Wildcards work too: ”East*” matches every region that starts with East, and a question mark matches exactly one character.
SUMIFS is where monthly totals live. For November 2026 the formula looks like this:
=SUMIFS(D2:D100, A2:A100, ”>=”&DATE(2026,11,1), A2:A100, ”<=”&DATE(2026,11,30))
Put the start and end dates in their own cells and reference those. Next month you change two cells instead of rewriting the formula.
Two limits matter. Every range must have the same number of rows as the sum range, or Excel returns #VALUE!. And SUMIFS only combines conditions with AND. For an OR condition, wrap an array constant in SUM: =SUM(SUMIFS(D2:D100,B2:B100,{”East”,”West”})) returns the East plus West total in one cell.
Planning home costs too? Read this next:
Running totals and per-region totals
A running total is =SUM($D$2:D2) in the first row, copied down. The dollar signs anchor the start of the range while the end cell moves one row at a time. The data has to be sorted by date for the running total to mean anything.
To keep a separate running total for each region, use =SUMIFS($D$2:D2,$B$2:B2,B2). It adds only the same-region amounts up to the current row. For a summary table with one row per region, =SUMIFS($D$2:$D$100,$B$2:$B$100,$G2) filled down does the whole table in one pass.
Keep the running total on the full data set. Once a filter is on, SUM will still count the hidden rows, so use SUBTOTAL for any total that someone reads off a filtered view.
Why a total comes out wrong
Most wrong totals come from the data, not the function. Numbers stored as text are the usual culprit. They tend to sit left-aligned, show a small green triangle in the corner, or carry a leading apostrophe. Manual calculation mode, a range that stops above newly added rows, and trailing spaces (which stop ”East” from matching ”East ”) are the other frequent causes.
The wrong total checklist is in the original post, and it walks through those causes in order. Start with the data types, then check calculation mode, range size, filters and stray characters. Doing it in that order keeps you from rewriting formulas that were never the problem.
Converting text to numbers has three reliable routes. The error indicator's Convert to Number option works for a handful of cells. Text to Columns handles a whole column in one pass. Paste Special with a copied 1 handles mixed formatting. Copy the sheet first so you can compare the before and after.
Wrapping a range in -- or SUMPRODUCT also forces text into numbers, but it hides the mess instead of removing it. I would fix the source column.
Five wrong total causes, checked in order, each with a fix:
Where to go after the basics
Learn SUM, SUMIFS and SUBTOTAL first. SUMIFS covers most of what SUMIF does, so you can drop one name from your head and still handle nearly every case. Get comfortable with the criteria syntax, because the operators, wildcards and cell references do most of the work.
If a lookup returns #N/A instead of a total, the causes are different and the VLOOKUP fix guide walks through them. When you want the wider function set on one page, a cheat sheet grouped by job is quicker than searching help.
Before you send a total anywhere, select the visible cells and compare the status bar sum with your formula. If the two numbers differ, stop and find out why. The status bar number is not stored in the file, so anything you report should come from a real formula in a cell.
Totals feed invoices, budgets and tax prep, so one cross-check costs seconds and saves a correction email later.
See the wrong total checklist on daylongs.com
Planning home costs too? Read this next: Also read: Home Insurance
Top comments (0)