Excel SUMIFS #VALUE! Error: Causes and Fixes

In Excel, SUMIFS most commonly returns #VALUE! when its sum range and one or more criteria ranges have different dimensions. Make those ranges the same size and shape; if they already match, check for a matching error value in the sum range and for references to a closed external workbook.

What the SUMIFS #VALUE! error means

SUMIFS adds values only when rows meet one or more conditions. A #VALUE! result means Excel could not complete that calculation, rather than simply finding no qualifying rows. That distinction matters: if the formula works but no records meet its conditions, the usual result is zero, not #VALUE!.

The first check should be the ranges, not the criteria text. A typo such as spelling a department name differently usually results in zero matches. By contrast, a range-size mismatch can make the formula invalid. Excel may display a different error for other problems, such as an invalid reference, so identify the exact error shown in the result cell before changing the formula.

How SUMIFS evaluates ranges

The function takes the cells to add first, then pairs each criteria range with a condition. For a reliable calculation, the sum range and every criteria range should cover corresponding cells: the same number of rows and columns, in the same layout. For example, a five-row sum range should be paired with five-row criteria ranges, not three-row ranges or a whole-column range.

Suppose a worksheet has region names in A2:A6, salesperson names in B2:B6, and sales amounts in C2:C6. Its rows contain East, Mina, 120; West, Omar, 90; East, Omar, 80; East, Mina, 150; and West, Mina, 60. With those ranges, this formula sums the East sales:

With regions in A2:A6 and sales amounts in C2:C6, the formula is =SUMIFS($C$2:$C$6,$A$2:$A$6,"East"). It returns 350, because 120 + 80 + 150 = 350.

To require two conditions, add another criteria-range and criteria pair. With regions in A2:A6, salesperson names in B2:B6, and amounts in C2:C6, this formula returns 270 for East sales handled by Mina: =SUMIFS($C$2:$C$6,$A$2:$A$6,"East",$B$2:$B$6,"Mina"). The 120 and 150 records meet both conditions.

The argument order is important: SUMIFS(sum_range, criteria_range1, criteria1, ...). This differs from the older SUMIF argument order, which places the criteria range first. Copying a SUMIF pattern into SUMIFS without adjusting the order can lead to an incorrect formula or result.

Fix range-size and range-shape mismatches

Compare the start and end cells in every range argument. In the example above, a criteria range that stops at row 4 is shorter than the five-row sum range. With amounts in C2:C6 and regions in A2:A4, this formula uses mismatched ranges and can return #VALUE!: =SUMIFS($C$2:$C$6,$A$2:$A$4,"East").

Correct it by extending the criteria range to A6, or by shortening the sum range to match the records you intend to include. The ranges must also have the same shape: a vertical five-cell range is not interchangeable with a horizontal five-cell range. Check every criteria pair, not just the first one, because a later pair can be the source of the error.

Using full-column references consistently is one way to avoid a mismatch as data grows. With the region and amount fields in columns A and C, respectively, this formula uses full columns for both: =SUMIFS(C:C,A:A,"East"). For a large workbook, bounded ranges or an Excel Table may be more efficient and easier to maintain. The key is consistency: do not pair a full-column sum range with a short criteria range.

When checking a long formula, temporarily remove criteria pairs one at a time. If removing one pair makes the error disappear, inspect that pair’s range dimensions and reference. Restore the pair with corrected references, then check the next condition if necessary.

Check external workbook references

A formula that refers to data in another workbook can return #VALUE! when the source workbook is closed. This is a known limitation for SUMIFS with external workbook references in Excel desktop. Open the source workbook and recalculate to see whether the formula then works. If it does, the closed-workbook link is a likely cause rather than a mismatch in the local data.

Check the link path as well. A moved or renamed source file can produce a broken reference, often shown as #REF! rather than #VALUE!. In Excel for Microsoft 365 on Windows, use Data > Workbook Links to review or update workbook links when that command is available for the file. Mac and web versions can present link-management options differently; if a linked formula behaves differently, check the source workbook and link status in desktop Excel.

Look for errors in matching sum cells

A matching row in the sum range must contain a usable value. If a qualifying row’s amount cell itself contains #VALUE!, the calculation can return that error instead of a total. Filter or inspect the rows that meet the criteria and look for error values in the cells being added. Correct the underlying error in the source formula where possible, rather than hiding it in the total.

Text in a sum range is a different issue. A number stored as text is generally not added as a numeric amount, so the result may be lower than expected or zero; that does not by itself mean SUMIFS has returned #VALUE!. Check whether the cell is genuinely numeric, and convert imported text values to numbers if appropriate. Also distinguish a true error value from a text string that merely looks like an error.

Use a systematic troubleshooting check

Work through these checks in order, changing one thing at a time. That makes it easier to identify the cause and avoid masking a data problem:

Confirm the result cell shows #VALUE!, not zero, #REF!, or another error.
Count the rows and columns in the sum range and every criteria range. Make their dimensions and orientation match.
Check the first and last row in each reference. Look for a missed row, an accidental shifted reference, or a range that stops before the latest data.
Temporarily remove additional criteria pairs to find whether a particular pair introduces the problem.
Inspect qualifying sum cells for error values, and verify that amounts intended for addition are numeric.
If any reference points to another workbook, open that workbook and recalculate.
After fixing the cause, check that the resulting total agrees with a few qualifying records calculated by hand.

Absolute references, such as $C$2:$C$6, can help when copying formulas because they keep the range from shifting. They do not repair a range that is already too short or incorrectly shaped. If the data is an Excel Table, structured references can also expand as rows are added, reducing the chance that a formula excludes new records; still verify that the sum and criteria columns refer to the intended fields.

Tell #VALUE! apart from other SUMIFS problems

A zero result is often a criteria or data issue, not a calculation error. The criterion might not match because of an extra space, a different spelling, a date stored as text, or a number stored in a different form. For example, if the formula searches for East but the data contains East followed by a space, Excel may find no match and return zero. Clean or standardize the source data when appropriate, then check the criteria again.

A total that is too small may indicate text values in the sum range, incomplete ranges, or records that fail one of the conditions. A total that is too large can point to duplicate records or a condition that is broader than intended. These outcomes are not the same as #VALUE!; first resolve the displayed error, then investigate whether the successful result is logically correct.

For a single condition, SUMIF may be a simpler choice, but it does not remove the need for suitable ranges. Use SUMIFS when you need multiple conditions, including when there is currently only one and you expect to add more later. Switching functions will not fix mismatched data references or an error in a qualifying amount cell.

Frequently Asked Questions

Why does SUMIFS return #VALUE! when the ranges look similar?
Ranges can look alike while ending on different rows, or one can be horizontal while another is vertical. Check each argument’s exact address and dimensions, including every additional criteria range.

Does a criteria that matches nothing cause #VALUE!?
No. When the formula is otherwise valid, no matching records normally produce zero. Check the spelling, spaces, date or number formats, and criteria logic if zero is unexpected.

Can SUMIFS add numbers stored as text?
Text values in the sum range generally are not added as numeric amounts. They can make the result incomplete, but they are not the usual explanation for a #VALUE! result. Check and convert imported values when needed.

Why does the formula work when the other workbook is open?
SUMIFS with an external workbook reference can return #VALUE! when the source workbook is closed. Open the source file to confirm whether that explains the behavior, and review the link if the workbook has moved.

Will changing SUMIFS to SUMIF fix the error?
Not if the underlying cause is a mismatched range, a closed external workbook, or an error in a matching sum cell. Use SUMIF for a single condition only when it better fits the calculation, and check its ranges as carefully.

Do the same range rules apply in Excel for Mac or the web?
The core SUMIFS argument order and range-matching checks are the same. External-link handling and some workbook-link controls can differ by platform, so verify those links in the Excel version where the file is being used.

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.