SUMIFS Excel Error? Causes, Checks, and Practical Fixes

A SUMIFS error in Excel usually comes from a misspelled function or reference, criteria written in the wrong form, or ranges that do not have matching dimensions. Check the exact error shown, confirm every criteria range matches the sum range in shape, and then test the criteria one at a time; if the formula returns 0 rather than an error, the criteria may simply match no rows.

What SUMIFS does and what an error means

SUMIFS adds numbers from a sum range only when corresponding cells meet one or more conditions. It is useful for questions such as “How much did we sell for this product in this region?” The function itself is available in current desktop versions of Excel, including Microsoft 365 for Windows. A formula can be syntactically valid and still return 0, so distinguish a displayed error such as #VALUE! from a result that is merely unexpected.

With product names in A2:A6, amounts in B2:B6, and payment statuses in C2:C6, the following formula adds amounts for paid apples:

=SUMIFS(B2:B6,A2:A6,"Apples",C2:C6,"Paid")

Assume the five rows are Apples, 5, Paid; Apples, 7, Paid; Apples, 4, Pending; Pears, 9, Paid; and Pears, 3, Pending, with each row listing product, amount, and status. The result is 12, because only the two paid-apple amounts, 5 and 7, qualify. In the formula, the sum range comes first, followed by pairs of criteria range and criterion. Each added condition must be satisfied for a row to contribute.

Read the exact error before changing the formula

The error message is a useful clue, but it does not identify every possible cause. Inspect the formula bar as well as the displayed cell. Look for an incorrect sheet name, a missing parenthesis, a range ending on the wrong row, or a criterion that is not written as intended.

#NAME? often means Excel cannot recognize the function name, a name used in the formula, or unquoted text. Check spelling and any named ranges. Text criteria such as Apples need quotation marks when typed directly in a formula.
#REF! indicates a broken reference, often because a referenced cell, column, or worksheet was deleted or moved in a way that invalidated the formula.
#VALUE! can occur when the sum range and a criteria range have different dimensions. It can also arise in some formulas using references to a closed external workbook. Check the range shapes first, then investigate linked workbooks if the ranges appear sound.
0 is not an error value. It commonly means no rows meet all the conditions, or that matching cells contain values stored as text rather than numbers.

If the cell displays a different error, trace the formula’s references and inspect errors in the source cells too. SUMIFS cannot produce a meaningful total from a range that contains a broken reference or another problem affecting its inputs.

Check range sizes and the SUMIFS argument order

The sum range and each criteria range should cover corresponding rows and columns. A frequent mistake is using B2:B100 as the sum range but A2:A99 as a criteria range. Another is accidentally selecting a whole column for one range and a short block of rows for another. Keep the ranges aligned: if the first data row is row 2 and the last is row 100, use those same row limits for each range.

SUMIFS argument order is another common source of confusion. The first argument is the cells to add, not the first criterion range. When adapting an older SUMIF formula, people sometimes put the criteria range first, which is the order used by SUMIF but not SUMIFS. With names in A2:A20 and sales in B2:B20, a total for Apples is:

=SUMIFS(B2:B20,A2:A20,"Apples")

The result is the sum of all numeric entries in B2:B20 whose corresponding name in A2:A20 is Apples. The example has one condition, but SUMIFS supports multiple criteria pairs. Add each new criteria range and its condition as a pair, and verify that every range lines up with the same records.

Write criteria correctly, especially comparisons and dates

For a direct text criterion, put the text in straight double quotation marks. For comparisons, place the operator and value together inside quotation marks. If the comparison value is in a cell, join the operator string to that cell with the ampersand. A criterion such as greater than 100 is not the same as typing a bare greater-than sign followed by a number as a separate formula argument.

With product names in A2:A10 and amounts in B2:B10, this formula totals amounts greater than 100:

=SUMIFS(B2:B10,A2:A10,">100")

It returns the sum of the qualifying amounts, not the count of qualifying rows. If the cutoff is in D1 instead, use this formula with the same A2:A10 and B2:B10 data:

=SUMIFS(B2:B10,A2:A10,">"&D1)

For dates, avoid typing an ambiguous date string into the criterion. With transaction dates in A2:A20 and amounts in B2:B20, this example sums transactions from January 2026:

=SUMIFS(B2:B20,A2:A20,">="&DATE(2026,1,1),A2:A20,"<"&DATE(2026,2,1))

The result is the total for dates on or after January 1 and before February 1, 2026. Using a start boundary and an exclusive next-month boundary also handles cells that contain times on the final day. The dates in the source range must be real Excel date values; text that merely looks like a date may not compare as expected.

Why a formula can return zero or the wrong total

A zero result often deserves the same careful check as an error. First confirm that the expected records actually satisfy every condition at once. SUMIFS uses AND logic across criteria pairs: a row must meet the product condition and the status condition, for example. If the intended rule is Apples or Pears, a single formula with both product criteria would ask for a row that is both products, which no ordinary row can be.

For a simple OR condition, add two SUMIFS results. With products in A2:A20 and amounts in B2:B20, this totals Apples or Pears:

=SUMIFS(B2:B20,A2:A20,"Apples")+SUMIFS(B2:B20,A2:A20,"Pears")

The result is the sum of the amounts for both product groups. This approach is straightforward when the alternatives are distinct. If categories overlap or the logic is more complex, check whether adding separate totals would count a record more than once.

Also examine the actual contents of the cells. A cell may contain an extra space, a different spelling, or a nonprinting character copied from another system. The text Apples is not an exact match for Apples followed by a space. Numeric-looking entries may be stored as text, so they may not behave like numbers in the sum range. Check number formats and, more importantly, the underlying values; changing a cell’s display format alone does not convert text to a number.

Wildcards, blanks, and text that looks like a criterion

SUMIFS supports wildcards for text criteria. An asterisk represents any sequence of characters, while a question mark represents one character. For example, a criterion of North* can match North, North-East, or North Region. A tilde escapes a wildcard when the character should be matched literally. Wildcards apply to text matching; they are not a general substitute for numeric comparisons.

With descriptions in A2:A30 and amounts in B2:B30, this formula totals rows whose description begins with North:

=SUMIFS(B2:B30,A2:A30,"North*")

The result is the sum of B2:B30 for descriptions beginning with those characters. For a blank criterion, use an empty quoted string; for cells that are not blank, use the not-equal operator with an empty string. Be precise about whether a cell is truly empty, contains a formula returning an empty string, or contains a space: those cases can look alike on screen but are not identical.

If a criterion comes from a cell, confirm that the cell contains the value you expect and that any comparison operator is joined correctly. A criterion cell that looks like a date, number, or category may contain text with hidden spaces. One practical diagnostic is to test a single condition first, then add the remaining pairs back one at a time. That reveals which condition stops matching.

A practical troubleshooting sequence

Use a controlled sequence rather than rewriting the whole formula at once. This keeps the original logic visible and makes it easier to identify the actual problem.

Click the result cell and read the exact error, or confirm that the result is 0 rather than an error.
Check the function name, parentheses, commas, quotation marks, and the order of arguments. In some regional settings Excel expects semicolons instead of commas as list separators; use the separator your installation accepts.
Compare the dimensions of the sum range and every criteria range. Make sure they cover corresponding records and have matching row and column counts.
Test the formula with just one criteria pair. Confirm that the criterion’s spelling, spacing, comparison operator, and data type match the source cells.
Add the other criteria pairs back individually. When the result changes unexpectedly, inspect that pair’s range and values.
Check for text-formatted numbers, dates stored as text, hidden spaces, and errors in source cells. Verify the underlying values rather than relying only on how cells look.
If needed, use Formulas > Evaluate Formula to step through how Excel interprets the calculation. This command is available in desktop Excel for Windows; ribbon details can differ on Mac and Excel for the web.

For a workbook that uses tables, structured references can make formulas easier to maintain as rows are added, provided the referenced table columns are correct. For ordinary ranges, absolute references such as $A$2:$A$100 can prevent ranges from shifting when a formula is copied. That does not repair a wrong range, so verify the boundaries before filling the formula down.

SUMIFS compared with SUMIF and other approaches

Use SUMIFS when the total must meet multiple conditions, or when you want the function name and argument pattern to remain consistent as conditions are added. SUMIF is designed for one condition and uses a different argument order: criteria range, criterion, then optional sum range. That difference is a common reason a formula copied from a SUMIF example does not work when changed to SUMIFS.

For one condition, SUMIFS can still be used, as shown earlier. It is often a convenient choice when a report may later need another condition. For a small fixed OR list, adding separate SUMIFS calculations is readable. For more involved analysis, a PivotTable may be easier to inspect and summarize by categories, while a filtered table can help you verify which rows qualify. These are alternatives for different workflows, not automatic fixes for a malformed formula.

Frequently Asked Questions

Why does SUMIFS return 0 when matching rows are visible?
Check whether every condition is satisfied by the same rows, and look for extra spaces, different text, or numbers and dates stored as text. A displayed value can look like a match without being the same underlying value.

Can SUMIFS use different-sized ranges?
Its sum range and criteria ranges should have matching dimensions and corresponding records. Mismatched range shapes can produce #VALUE! or an incorrect result, so align their starting and ending rows.

Does SUMIFS use AND or OR logic?
Separate criteria pairs are combined with AND: a row must meet all of them. For a simple OR between alternatives, add separate SUMIFS calculations, taking care not to double-count overlapping groups.

Why does SUMIFS show #NAME?
Check that SUMIFS is spelled correctly, that any named ranges exist, and that directly typed text criteria are enclosed in straight double quotation marks. Also check for missing punctuation or an unrecognized name in the formula.

Can SUMIFS criteria include dates or greater-than conditions?
Yes. Use comparison operators as part of a quoted criterion, and join an operator to a cell reference when the cutoff is stored in a cell. For date ranges, real Excel dates and clear inclusive or exclusive boundaries reduce ambiguity.

Why does a SUMIFS formula use semicolons instead of commas?
Excel’s list separator depends on regional settings. Some installations require semicolons between arguments. If a comma-separated formula is rejected, use the separator configured for that installation; the function’s argument order remains the same.

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.