Excel SUM SUMIF SUMIFS Guide: How to Add Up Numbers and Fix Totals That Are Off

How do you add numbers in Excel with SUM, SUMIF and SUMIFS?

Use =SUM(D2:D100) to add a whole range, SUMIF when one condition decides which rows count, and SUMIFS when several conditions must all be true. When a filter is on and you only want the visible rows, use =SUBTOTAL(109, D2:D100) instead.

That last distinction causes more wrong totals than any typo. People trust the number in the footer, and the footer was built with a function that does not know a filter exists.

Every example below uses an invented sales log: dates in column A, region in B, product in C, and amount in D, with data in rows 2 through 100. Swap in your own ranges. Excel versions differ slightly in menu names, so treat the click paths as a guide and check them in your own file.

Which function should you pick for which job?

Here is the comparison I keep in my head, laid out once so you do not have to rebuild it every time.

Function	Job	Example	Common trap
SUM	Add a whole range	=SUM(D2:D100)	Skips numbers stored as text
SUMIF	One condition	=SUMIF(B2:B100,"East",D2:D100)	Sum range comes last
SUMIFS	Several conditions (AND)	=SUMIFS(D2:D100,B2:B100,"East",C2:C100,"A")	Sum range comes first; mismatched sizes give #VALUE!
SUBTOTAL	Respect filters and hidden rows	=SUBTOTAL(109,D2:D100)	9 vs 109 is easy to mix up
SUMPRODUCT	Multiply, then add	=SUMPRODUCT(E2:E100,F2:F100)	Ranges must be the same size

The argument order flip between SUMIF and SUMIFS trips up nearly everyone once. SUMIF reads “look here, for this, add that.” SUMIFS reads “add this, looking here, for that.” My own habit is to use SUMIFS even for a single condition. One argument order to remember, and adding a second condition later is a copy-paste job.

Microsoft describes SUMIFS as adding the arguments that meet multiple criteria, and the syntax is laid out on Microsoft’s SUMIFS function page.

What does SUM actually do, and what does it skip?

SUM adds every number in the arguments you give it. Inside a range reference, it ignores text, logical values and empty cells rather than throwing an error.

Four ways to feed it:

• =SUM(D2:D100) for a continuous range
• =SUM(D2:D10, D20:D30) for separate blocks
• =SUM(D:D) for a whole column, safe only if nothing else numeric sits in that column
• Alt + = to have Excel write the AutoSum formula for you

Whole-column references are tempting, but if you put the total at the bottom of the same column, the formula includes itself and Excel flags a circular reference. For a sheet that holds its total under the data, stop the range at the last data row.

If the log grows every month, turn it into a table with Ctrl+T. Then =SUM(Table1[Amount]) expands on its own as rows are added, which is the cleanest cure for the “my range stopped at row 100” problem.

How do you add up rows that meet one condition with SUMIF?

The pattern is =SUMIF(criteria_range, criteria, sum_range). East region only: =SUMIF(B2:B100,"East",D2:D100).

The criteria slot is more flexible than it looks.

Goal	Formula
Amounts of at least 1,000	=SUMIF(D2:D100,">=1000")
Region matching the value in F1	=SUMIF(B2:B100,F1,D2:D100)
Amounts above the number in F1	=SUMIF(D2:D100,">"&F1)
Regions starting with “East”	=SUMIF(B2:B100,"East*",D2:D100)
Three-character product codes	=SUMIF(C2:C100,"???",D2:D100)
Rows where region is not blank	=SUMIF(B2:B100,"<>",D2:D100)

Comparison operators go inside the quotes. When the threshold lives in a cell, join the two with an ampersand. Learn that one move and most SUMIF problems stop being problems. If you skip the sum range, Excel adds the criteria range itself, which is what the >=1000 example relies on. Microsoft documents the arguments on its SUMIF function page.

How does SUMIFS multiple criteria work?

The shape is =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...), and a row counts only when every condition is true. East region and product A is =SUMIFS(D2:D100,B2:B100,"East",C2:C100,"A").

Monthly totals are where this earns its keep:

=SUMIFS(D2:D100, A2:A100, ">="&DATE(2026,11,1), A2:A100, "<="&DATE(2026,11,30))

That adds amounts dated November 1 through 30. Better still, put the start date in G1 and the end date in G2 and write ">="&G1 and "<="&G2. Next month you change two cells, not the formula.

Two rules to respect. Every range must have the same number of rows as the sum range. And conditions combine with AND only. For “East or West,” either add two SUMIFS or wrap an array constant in SUM:

=SUM(SUMIFS(D2:D100, B2:B100, {"East","West"}))

People often assume OR is impossible and build a helper column. The array trick does it in one cell.

A few details save debugging time. Text criteria in SUMIF and SUMIFS are not case sensitive, so “east” and “East” match the same rows. A criterion that looks like a number, such as “100”, will match both the number 100 and the text “100”, which can quietly pull in values you did not expect if your data is mixed. And if you need to match a literal asterisk or question mark, put a tilde in front of it, as in "~*", otherwise Excel treats it as a wildcard.

Another habit worth building: keep the criteria in labeled cells instead of burying them in the formula. A small block with “Region,” “Start date” and “End date” labels above the inputs turns your sheet into a mini report that a colleague can change without opening the formula bar. It also makes it obvious when someone has typed a date as text, which is one of the most common reasons a SUMIFS date filter returns zero. If the dates are real dates, they right-align and respond to a change in number format; if they do not, you have found your problem.

One more point on sizes. SUMIFS wants every range to be the same height and width. If you select D2:D100 for the sum range and B2:B99 for a criteria range, Excel returns #VALUE! rather than guessing. SUMIF is more forgiving about the sum range, but relying on that forgiveness is how misaligned totals sneak in, so keep ranges identical in both functions.

Finally, remember that none of these functions look at formatting. A cell that is shaded red or has a strikethrough still counts toward the total, so if you “cross out” cancelled orders visually, the totals will not change. Add a status column such as “Active” or “Cancelled” and let SUMIFS filter on it. That keeps the logic in the data, where the next person can see it.

Why does Excel SUM return 0 or the wrong total?

Almost always the data is the problem, not the function. Work through this list in order.

1. Are the numbers really numbers? Left-aligned values, a green triangle in the corner, or a leading apostrophe mean text. CSV exports, pasted web tables and system downloads are the usual sources.
2. Is calculation set to manual? On the Formulas tab, check Calculation Options. In manual mode nothing updates until you press F9.
3. Does the range reach the last row? New rows added below an old fixed range are silently left out.
4. Is a filter or hidden row involved? SUM counts hidden rows. If your eyeball total differs, this is the likely reason.
5. Are there invisible characters? A SUMIF for “East” will not match “East ” with a trailing space. TRIM cleans that.

Converting text to numbers has three reliable routes. Copy the sheet first.

Method	Steps	Best for
Error indicator	Select a flagged cell, click the warning icon, choose Convert to Number	A handful of cells
Text to Columns	Select the column, Data tab, Text to Columns, Finish	A whole column at once
Paste Special multiply	Copy a blank cell holding 1, select the range, Paste Special, Multiply	Mixed formatting

A formula such as =SUMPRODUCT(--D2:D100) also forces text into numbers, but it hides the underlying mess. I would rather fix the source. For broader trouble when formulas will not calculate at all, see the walkthrough in Excel formulas not working: causes and fixes.

How do you total only filtered or visible rows with SUBTOTAL?

Write =SUBTOTAL(109, D2:D100). The first argument chooses the operation, and for addition there are two codes whose difference matters.

function_num	Operation	Rows hidden by hand	Rows hidden by a filter
9	Sum	Included	Excluded
109	Sum	Excluded	Excluded

Per Microsoft’s documentation, codes 1 to 11 include manually hidden rows and codes 101 to 111 exclude them, while filtered-out rows are excluded either way. The full code list is on Microsoft’s SUBTOTAL function page. Hiding a column does not change the result, because SUBTOTAL’s hiding logic works on rows.

There is a bonus: SUBTOTAL skips other SUBTOTAL results inside its range, so a grand total over a sheet with several subtotals will not double count.

How do you build running totals and totals by category?

For a running total, enter =SUM($D$2:D2) in the first row and copy down. The start is anchored with dollar signs while the end cell moves, so the range grows by one row each step.

Row	Amount (D)	Running total formula (E)	Result
2	1,200	=SUM($D$2:D2)	1,200
3	800	=SUM($D$2:D3)	2,000
4	500	=SUM($D$2:D4)	2,500

These figures are made up for illustration. To keep a separate running total per region, use =SUMIFS($D$2:D2,$B$2:B2,B2), which adds only same-region amounts up to the current row. And 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.

If you are tracking personal spending this way, pair it with the layout in an Excel budget spreadsheet template. For the budgeting method itself, the 50/30/20 household budget guide covers how to split the categories.

What does a typical SUM mistake look like?

This is a composite of situations that come up constantly, not one real person’s story.

An analyst filters a sales sheet to the East region and copies the total from the footer, =SUM(D2:D100), into a weekly report as “East sales.” The footer actually holds the all-regions total, because SUM counts rows the filter has hidden. A manager comparing the report with the source system spots the gap, and the report goes out again with a correction note.

The fix was one function. A footer on a filterable table should be =SUBTOTAL(109,D2:D100) from day one. The second safeguard costs five seconds: after filtering, compare the status bar sum of the visible column with your formula. If they differ, stop and find out why.

Where should you go after the basics?

I would learn SUM, SUMIFS and SUBTOTAL first and treat everything else as look-it-up material. SUMIFS covers the same ground as SUMIF, so you can retire one name from your head.

Natural next steps:

• Lookups are the other half of most reports. If one throws an error, the guide to fixing VLOOKUP #N/A is the quickest route out.
• For a map of the whole function set, start with the Excel functions cheat sheet and the older 10 essential Excel formulas.
• If you track money monthly and the spreadsheet is becoming a chore, the comparison of budgeting apps shows when an app is the better tool.

Totals feed decisions: invoices, budgets, tax prep. The habit of cross-checking a number once before sending it is worth more than any advanced formula.

This article is general information about Excel, written for US users of Microsoft 365 and recent desktop versions; the sample data is invented. Menu names and behavior can vary by version and language settings, so test formulas on a copy of your file before relying on them for important documents. Information as of November 2026.