SUMIF Function Not Working? Fix Common Excel Errors

If SUMIF is returning zero, the wrong total, or an error, check that its criteria actually match the cells in the criteria range and that the sum range covers the intended values. In Excel for Microsoft 365 on Windows, the basic form is =SUMIF(range, criteria, [sum_range]); correcting a mismatch in text, numbers, dates, or references usually fixes the problem.

What SUMIF Does and How Its Arguments Work

SUMIF adds values associated with cells that meet one condition. Its first argument is the range Excel checks, the second is the condition, and the optional third argument is the range containing the values to add. The criteria range and sum range are often different columns, but they should normally have the same number of rows.

For example, with product names in A2:A6 and sales amounts in B2:B6, the formula =SUMIF(A2:A6,"Apples",B2:B6) returns 20 if the two Apple rows contain 12 and 8. Excel checks each name in column A and adds the value from the corresponding row in column B only when the name matches. If the sum range is omitted, SUMIF adds qualifying cells in the criteria range itself, which is useful when the condition and numbers are in the same column.

Use the arguments in this order: =SUMIF(range, criteria, [sum_range]). The square brackets in the syntax indicate that sum_range is optional; do not type the brackets into a formula. A missing comma, an incorrectly placed argument, or a range that starts on the wrong row can make a formula invalid or produce a plausible but incorrect result.

Start With a Simple Diagnostic Check

Before changing the formula, identify what it should count and add manually a few matching values. Then inspect the formula bar and confirm which cells each argument refers to. In Excel for Microsoft 365 on Windows, selecting the formula cell displays the formula in the formula bar; selecting a reference while editing can also help identify the range it points to.

Confirm the criteria range contains the labels or numbers you intend to test.
Check the criteria spelling, operators, and cell references, including any spaces.
Verify that the sum range contains numeric values and lines up with the criteria range.
Temporarily test a known label or number, then compare the result with a hand calculation.
Check whether the cell displays a formula instead of a result, or whether Excel calculation is set to Manual.

If Excel shows the formula text in the cell, the cell may be formatted as Text, or the formula may begin with an apostrophe. Change the cell format to General, then re-enter the formula; changing the format alone may not recalculate text already entered. Also check that the first character is an equals sign and that there is no leading space before it.

Criteria That Do Not Match the Data

A frequent cause of a zero result is a criterion that looks right but is not identical to the data. A label may have a leading or trailing space, a different spelling, or a nonbreaking space copied from a website. Ordinary spaces can be spotted by editing the cell or comparing its contents with a clean entry. The TRIM function can remove many ordinary extra spaces, but it does not remove every nonbreaking space automatically.

SUMIF text comparisons are not case-sensitive: Apples and APPLES match the same criterion. If capitalization must distinguish records, SUMIF alone is not sufficient; a case-sensitive approach requires a different formula design. For ordinary category totals, case usually is not the issue, while hidden spaces and inconsistent spelling often are.

For a criterion stored in another cell, reference that cell rather than typing a second version of its contents. With categories in A2:A20, amounts in B2:B20, and the desired category in E2, use =SUMIF(A2:A20,E2,B2:B20). This can avoid spelling discrepancies and makes the result respond when the selection in E2 changes. Check that E2 does not itself contain extra spaces.

Numbers, Operators, and Dates

Numeric criteria can be written as a number, or as text containing a comparison operator. For instance, with test values in A2:A6 and corresponding amounts in B2:B6, =SUMIF(A2:A6,">100",B2:B6) adds the amounts for rows whose A value is greater than 100. The comparison operators include greater than, less than, greater than or equal to, less than or equal to, and not equal to.

When the operator or threshold comes from a cell, join them with the ampersand. If A2:A20 contains quantities, B2:B20 contains revenue, and E2 contains the threshold, =SUMIF(A2:A20,">"&E2,B2:B20) adds revenue for quantities above the value in E2. Typing the criterion as “>E2” would instead look for text beginning with those characters; it would not use the value in E2.

Dates can fail for the same reason as numbers: a date that looks valid may actually be text. Excel stores real dates as serial numbers, so check whether the cell behaves like a date when its format changes. With dates in A2:A100, amounts in B2:B100, and a real Excel date in E1, =SUMIF(A2:A100,">="&E1,B2:B100) sums amounts dated on or after E1. To cover a single day, use a lower bound and an exclusive next-day upper bound in two SUMIFS conditions, or create a helper column; a single SUMIF supports only one condition.

For decimal values, check whether the workbook uses the expected decimal separator and whether the values are numeric rather than text imported from another system. Excel’s regional settings can affect how numbers and formula argument separators are entered. If commas are rejected in a formula, the local Excel configuration may require semicolons as separators; use the separator accepted by that installation.

Check the Sum Range and Its Alignment

When a result is too large, too small, or consistently shifted by one row, compare the starting and ending rows in both ranges. With product names in A2:A10 and amounts in B2:B10, the intended references are generally A2:A10 and B2:B10. A range such as B1:B9 does not correspond to those same records, so matching criteria can be paired with the wrong values.

Excel can accept a sum_range whose dimensions differ from the criteria range, but it uses the top-left cell of sum_range and adjusts the area to match the criteria range. That behavior can be surprising and is not a good substitute for deliberate alignment. Use ranges of the same shape and size so the formula is easier to audit, especially when copying it down or extending a table.

Also distinguish a blank result from a zero result. SUMIF returns zero when no cells qualify or when qualifying sum cells do not contain numbers. A blank-looking cell might instead be formatted to hide zero values. Click the result and inspect the formula bar, then temporarily apply a number format that displays zero if necessary.

Wildcards, Blank Cells, and Other Criteria Rules

For text criteria, SUMIF supports wildcards. An asterisk matches any sequence of characters, and a question mark matches one character. With names in A2:A20 and values in B2:B20, =SUMIF(A2:A20,"East*",B2:B20) sums rows whose labels begin with East, such as East Coast. Wildcards are useful for deliberately partial matches, but they can also make a criterion too broad.

To search for a literal asterisk or question mark instead of treating it as a wildcard, precede it with a tilde. For example, a criterion containing ~* searches for an actual asterisk character. Criteria can also identify empty or nonempty cells: a criterion of "" targets blanks, while "<>" targets nonblank cells. Check the data before relying on blank criteria, since formulas returning an empty string can appear blank without being truly empty.

SUMIF cannot apply several independent conditions in one call. For example, summing sales where the region is East and the product is Apples requires a multi-condition formula rather than adding a second condition to SUMIF. Do not put an extra range and criterion into the SUMIF argument list; Excel will report too many arguments or interpret an invalid formula.

Formula Errors and Calculation Settings

A #VALUE! error can occur when SUMIF refers to a closed external workbook in certain circumstances; opening the source workbook may resolve it. Errors can also arise from invalid references or malformed formulas. If the formula is syntactically accepted but the value does not update after editing source cells, check Formulas > Calculation Options > Automatic in Excel for Microsoft 365 on Windows. In Manual calculation mode, press F9 to recalculate the workbook.

Check whether a leading apostrophe is forcing the entry to text, and whether Show Formulas is enabled. The Show Formulas command is on the Formulas tab; when it is active, cells display formulas instead of calculated results. Turning it off restores the normal display. On Mac, the same calculation and formula-display controls are available from Excel’s ribbon, though keyboard shortcuts can differ by keyboard layout and version.

If you copied a formula, inspect whether the references shifted. Relative references such as A2:A20 change when copied to another cell. Use dollar signs for a range that must stay fixed: with categories in A2:A20, values in B2:B20, and a criterion in D2, =SUMIF($A$2:$A$20,D2,$B$2:$B$20) keeps the data ranges fixed while allowing the criterion reference to change if the formula is filled down.

When SUMIFS or Another Method Is Better

Use SUMIF when a total depends on one condition. Use SUMIFS when it depends on two or more conditions, such as a particular region and a date period. SUMIFS places the sum range first, followed by pairs of criteria ranges and criteria, so its argument order differs from SUMIF. This is a common source of errors when switching between the functions.

A PivotTable may be more useful when you need to summarize a large list by several categories, compare groups, or change the grouping interactively. A helper column can also simplify complicated criteria by assigning each row a clear label, then using SUMIF on that label. These alternatives do not mean SUMIF is broken; they are better fits when the calculation asks more than one question at a time.

Frequently Asked Questions

Why does SUMIF return 0 when matching values are visible?
Check for hidden spaces, text-versus-number mismatches, an incorrect criteria cell, or a sum range containing text instead of numeric values. Also verify that the criteria range covers the rows you expect.

Can SUMIF use a cell reference as its condition?
Yes. Put the criterion in a cell and use that cell as the second argument, such as =SUMIF(A2:A20,E2,B2:B20). For an operator plus a cell value, join them, as in ">"&E2.

Does SUMIF distinguish uppercase and lowercase text?
No. SUMIF’s text matching is case-insensitive, so capitalization alone will not separate categories. Use a case-sensitive formula approach if letter case is meaningful in the data.

Why does SUMIF add the wrong row’s value?
The criteria and sum ranges may start or end on different rows. Make their dimensions match and confirm that corresponding cells refer to the same records.

Can SUMIF add values using multiple conditions?
No. SUMIF handles one condition. For multiple conditions, use SUMIFS, checking carefully that its sum range is the first argument and each condition is supplied as a range-and-criterion pair.

Most SUMIF problems come down to an incorrect match, a misaligned range, a value stored in the wrong type, or calculation/display settings. Inspect those items in order, test a small known example, and keep the criteria and sum ranges the same size. Once the formula’s logic is clear, the result is much easier to verify and maintain.

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.