Excel SUMIF: Avoid Errors and Fix Common Problems

Errors & fixes · Updated November 19, 2026

Excel’s SUMIF can return an error when a cell it needs to add contains an error value; to total matching rows while treating errors as zero, use SUMPRODUCT with IFERROR. If you only want to suppress an error message, you can wrap SUMIF in IFERROR, but that hides every error from the calculation, including problems you may need to fix.

What SUMIF Does—and What “Not Error” Can Mean

SUMIF adds values in a range when corresponding cells meet one condition. It is useful for totals such as sales by region, expenses by category, or hours logged by employee. The phrase “SUMIF not error” can refer to two different goals: preventing an error in the result, or getting a numeric result instead of an error that is already occurring. The right remedy depends on which cells are faulty and whether they should count as zero.

For example, suppose a sales table has regions in A2:A6 and sales amounts in B2:B6. The regions are East, West, East, West, and East; the amounts are 120, 90, 160, 80, and 130. With that layout, this formula returns 410, adding only the East sales:

With regions in A2:A6 and sales amounts in B2:B6, use:

=SUMIF(A2:A6,"East",B2:B6)

If a qualifying amount is an error value such as #N/A or #VALUE!, the total may show that error instead of a number. A sensible fix is to handle the faulty source value, or deliberately exclude it from the total. Simply replacing the displayed error with zero is not always the same as correcting the data.

SUMIF Syntax and Matching Rules

The function has three arguments: range is the cells Excel checks, criteria is the condition, and sum_range is the cells to add. The last argument is optional. If you leave it out, Excel sums the cells in the checked range itself.

With categories in A2:A20 and amounts in B2:B20, the general form is:

=SUMIF(A2:A20,"Travel",B2:B20)

The criteria can be text, a number, a comparison, or a wildcard expression. Text criteria such as "Travel" are not case-sensitive. To use a comparison operator, put it inside quotation marks. If the comparison value is in a cell, join the operator and cell reference with &.

  • With product names in A2:A20 and amounts in B2:B20, =SUMIF(A2:A20,"Notebook",B2:B20) totals rows labeled Notebook.
  • With amounts to test in A2:A20 and amounts to add in B2:B20, =SUMIF(A2:A20,">100",B2:B20) adds corresponding values where the tested amount is greater than 100.
  • With amounts in A2:A20, a threshold in D1, and values to add in B2:B20, =SUMIF(A2:A20,">"&D1,B2:B20) uses the value in D1 as the threshold.

Keep the checked range and sum range the same shape and aligned row by row. Excel can use ranges of different sizes, but the correspondence may not be what you intended; matching dimensions make the formula easier to verify and reduce confusing results.

Ignore Error Cells While Adding Matching Rows

When an error in a matching sum cell should contribute nothing, use SUMPRODUCT to test the condition and add values after IFERROR replaces errors with zero. This handles each sum cell individually rather than letting one matching error spoil the whole total.

Suppose A2:A7 contains East, West, East, East, West, East, while B2:B7 contains 120, 90, 160, #N/A, 80, and 130. If errors should be excluded and the numeric East values should still count, use:

With regions in A2:A7 and sales amounts in B2:B7, use:

=SUMPRODUCT((A2:A7="East")*IFERROR(B2:B7*1,0))

The result is 410: 120 + 160 + 130. The *1 converts numeric text to numbers; if a cell contains an error or nonnumeric text, the multiplication produces an error that IFERROR changes to zero. Blank cells also contribute zero. This approach is useful for a modest, fixed range, but it is important to understand the rule it applies: all errors and nonnumeric entries in the sum cells are treated as zero, including ones that might signal a data problem.

If you want to retain the simple SUMIF calculation, another option is a helper column. In C2:C7, put a cleaned version of the sales amount and fill the formula down through C7. With sales amounts in B2:B7, enter in C2:

=IFERROR(B2,0)

Then, with regions in A2:A7 and cleaned amounts in C2:C7, total East sales with:

=SUMIF(A2:A7,"East",C2:C7)

This also returns 410 for the example. A helper column makes the treatment visible and allows you to inspect which source values are errors. If nonnumeric text should also become zero, adjust the cleanup rule rather than assuming that every text entry is a valid amount.

When IFERROR Is Appropriate—and When It Is Not

The quickest way to stop an error from appearing in a cell is to wrap the entire calculation in IFERROR. For instance, the following returns zero whenever the SUMIF calculation produces any Excel error:

With regions in A2:A7 and sales amounts in B2:B7, use:

=IFERROR(SUMIF(A2:A7,"East",B2:B7),0)

This does not repair or selectively skip a bad amount. If the result is an error because a matching sales cell contains #N/A, Excel returns zero for the entire formula—even if other matching rows contain valid sales. That is different from the 410 returned by the cell-by-cell error-handling example.

Use this wrapper when zero is a deliberate fallback, such as a dashboard where an unavailable calculation should display as zero and the underlying cause is checked elsewhere. Avoid it as a blanket fix during analysis: it can conceal a misspelled reference, an unexpected error in source data, or another formula problem. If a blank display is more appropriate, the fallback can be an empty text value, but that produces text rather than a numeric zero and may affect later calculations.

Diagnose Common SUMIF Errors and Wrong Totals

Before changing a formula, identify whether the result is an Excel error such as #VALUE! or merely a number that seems wrong. A wrong total usually points to criteria, range alignment, or data formatting; an error often points to a value included in the calculation or an invalid reference.

  • Check matching sum cells. Filter the category column to the criterion and look at the corresponding sum cells. If a matching amount contains #N/A or another error, decide whether to fix it, exclude that row, or treat it as zero.
  • Check the range endpoints. If the category range starts at row 2 but the sum range starts at row 3, Excel pairs the wrong rows. Use ranges with the same number of rows and matching first and last records.
  • Check the criterion text. Extra spaces or slightly different labels can prevent a row from matching. A wildcard can help with consistent prefixes, but it should not be used to conceal inconsistent data.
  • Check numbers stored as text. A value that looks numeric may be text, so SUMIF may not add it as a number. Convert the source values to numbers or use a carefully designed helper column.
  • Check relative references when copying. A range can shift when a formula is filled down. Use dollar signs to lock ranges if every copied formula should refer to the same data block.

For an error result, select the formula cell and inspect the formula bar; then evaluate the relevant source cells. In Microsoft 365 for Windows, Formulas > Evaluate Formula steps through a formula’s calculation. On Mac, the Evaluate Formula command is also available from the Formulas tab in current Excel versions, though ribbon layout can vary. In Excel for the web, use the formula bar and inspect the referenced cells; some desktop formula-auditing tools may not be available there.

SUMIF, SUMIFS, and SUMPRODUCT Compared

Choose the function that describes the condition, not simply the one that makes an error disappear. SUMIF is the straightforward choice for one condition. For example, it can total East sales. If you need to total East sales for a particular month as well, SUMIFS is designed to sum with multiple conditions. Its argument order differs from SUMIF: the sum range comes first, followed by pairs of criteria ranges and criteria.

SUMPRODUCT is a flexible alternative when the calculation needs to handle each row or combine conditions. In the error-skipping example, it lets IFERROR act on individual values before they are added. That flexibility can make a formula harder to read, so a helper column may be preferable when other people need to audit the cleanup rule. All three functions are available in Microsoft 365 Excel for Windows, Mac, and the web.

Do not switch to SUMIFS expecting it to ignore errors in qualifying sum cells automatically. Changing the number of criteria does not define how bad source values should be handled. Decide that policy explicitly, then use cleaned data, a helper column, or an error-aware calculation.

Frequently Asked Questions

Does SUMIF ignore errors?
If an error is in a sum cell that meets the criterion, the result can be an error rather than a numeric total. To exclude bad values while adding other matching rows, clean the sum data or use an approach such as SUMPRODUCT with IFERROR.

How do I make SUMIF show zero instead of an error?
Wrap the calculation in IFERROR and choose zero as the fallback. Remember that this changes any error from the complete calculation to zero; it does not skip only the faulty row.

Why does SUMIF return 0 when matching rows exist?
Check that the criterion matches the cell contents and that the intended sum range is aligned with the criteria range. Also check for extra spaces, different labels, and numbers stored as text in the sum range.

Can SUMIF add numbers stored as text?
Text values that look like numbers may not be included as numeric amounts. Convert them to real numbers, or use a helper column that converts valid numeric text while handling other entries deliberately.

Can SUMIF use more than one condition?
Use SUMIFS when a total must meet multiple conditions, such as a region and a date range. It does not by itself solve errors in the values being summed; handle those separately.

Choose an Error Rule You Can Explain

The safest fix is usually to correct an invalid source value. If that is not possible, decide whether the row should be omitted, counted as zero, or shown as an error that needs attention. Use a helper column when transparency matters, and use IFERROR around the whole formula only when suppressing every calculation error is genuinely the intended behavior. A clear rule makes the total easier to trust and easier for someone else to check.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.