SUMIFS Value Error? Fix #VALUE! in Excel

A SUMIFS formula that returns #VALUE! most often has sum and criteria ranges that are different sizes or shapes. Make every criteria range match the sum range exactly; if the ranges are already aligned, check whether the formula refers to a closed external workbook or contains an error in another referenced cell.

What a SUMIFS #VALUE! error means

SUMIFS adds numbers from one range only when corresponding cells meet one or more conditions. The #VALUE! result means Excel cannot evaluate the formula as written; it is different from a valid formula that returns zero because no rows matched. The best first check is the range geometry: count the rows and columns in every range argument and make sure they line up.

For example, if the sum range covers rows 2 through 100, each criteria range should also cover rows 2 through 100. A criteria range that starts at row 1, ends at row 99, or spans a different number of columns may cause an error. Excel does not automatically resize the other ranges to compensate.

Not every unexpected result is a #VALUE! error. SUMIFS normally ignores text in the sum range, and it can return 0 when no records match. A misspelled function name generally produces #NAME?, while an invalid cell reference may produce #REF!. Identify the exact error shown in the cell before changing the formula.

How SUMIFS evaluates its ranges

SUMIFS uses this argument order: the cells to add come first, followed by pairs of criteria ranges and criteria. The order matters: it differs from SUMIF, which puts its criteria range first. A valid two-condition formula can add sales in one region that have a particular status.

With regions in A2:A6, sales in B2:B6, and order statuses in C2:C6, this formula adds sales for West orders marked Paid:

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

The example returns 320: the qualifying sales are 120 and 200. The ranges all cover rows 2 through 6, so each sales value is checked against the region and status on the same row. This alignment is the central requirement, whether a formula has one condition or several.

In Microsoft 365 for Windows, commas separate function arguments under the usual regional settings. Some computers use semicolons because of their regional list-separator settings; if Excel rejects an otherwise correct formula as a syntax problem, check the separator your installation expects. That syntax problem is not the same thing as a calculated #VALUE! result.

Fix ranges with different sizes or shapes

Compare the first and last cell in every range argument. Each criteria range must have the same dimensions as the sum range—not merely the same number of cells. For example, a range of five rows by one column does not match a range of one row by five columns. The row positions must also correspond to the same records.

With amounts in B2:B6 and categories in A2:A5, this intentionally mismatched formula returns #VALUE! because one criteria range has four rows while the sum range has five:

=SUMIFS(B2:B6,A2:A5,"West")

If the category data really continues through row 6, extend the criteria range to match. With categories in A2:A6 and amounts in B2:B6, the corrected formula is:

=SUMIFS(B2:B6,A2:A6,"West")

If the data ends at row 6 and the West amounts are 120, 45, and 200, the corrected formula returns 365. Check that the first row of each range represents the same record as well as confirming the ending row. Matching dimensions alone will not correct a formula that pairs a sales row with the wrong category row.

Check the sum range and every criteria range for matching row and column counts.
Check that all ranges begin and end on the intended records.
When copying a formula, inspect whether relative references shifted. Use absolute references such as $A$2:$A$100 when a range should stay fixed as you copy the formula.

Check criteria and the source data

A criterion can be text, a number, a comparison such as greater than a value, or a reference to another cell. A criterion that does not match the data usually leads to zero, not #VALUE!. Still, checking criteria is useful after range dimensions are confirmed, because it can reveal a separate issue hidden behind the original error.

For a text criterion, confirm spelling and spaces. “West” and “West ” may not behave as you expect if the source data contains an extra trailing space. SUMIFS is not case-sensitive for ordinary text comparisons. If a criterion is held in a cell, make sure that cell contains the intended value and that a formula in it is not itself returning an error.

For dates, make sure the source cells contain real Excel date values rather than text that only looks like a date. A clear way to include all dates in a month is to use a lower bound for the first day and an exclusive upper bound for the first day of the next month.

With dates in A2:A4 and amounts in B2:B4, where the rows contain Jan. 5, Jan. 20, and Feb. 2, 2026, with amounts of 100, 50, and 80 respectively, this formula sums January amounts:

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

It returns 150, because the first two dates fall in January and 100 plus 50 equals 150. The exclusive February boundary also includes dates that have times attached on Jan. 31, unlike a criterion that stops at midnight at the start of that day. If this formula produces an error, verify the range sizes and that the dates are numeric date values.

Look for errors in referenced cells

SUMIFS cannot produce a useful sum if a cell it needs to evaluate already contains an error. Scan the sum range and criteria ranges for errors such as #VALUE!, #REF!, or #N/A, particularly if the problem began after importing data or changing a lookup formula. In Excel, Find can help locate error values in a large worksheet, but inspecting the formula bar and surrounding rows is still important: the error may come from an upstream calculation.

Also check whether a formula argument accidentally points to a cell containing an error as its criterion. A condition based on a valid text or number cell is different from a condition based on a cell whose formula has failed. Fix the upstream error or correct the reference first, then recalculate SUMIFS.

Numbers stored as text can make a total appear incomplete because text values in the sum range are not added as numbers. That usually does not explain #VALUE! by itself, but it can explain a total that is too small after the formula error is resolved. Use Excel’s number-format and data-cleaning tools to confirm that amounts are numeric rather than relying on how they look on screen.

Check links to other workbooks

A SUMIFS formula may refer to data in another workbook. In some external-reference situations, Excel can return #VALUE! when the source workbook is closed, even though the formula works while that workbook is open. This is a known issue to consider when the error appears only after closing or moving the source file; it is not proof that every closed-workbook reference will fail.

Open the source workbook and see whether the result recalculates. If that resolves the error, consider keeping the source available while calculations run, importing the needed data into the current workbook, or using a supported data connection appropriate to your workflow. Also check that the source file has not been renamed or moved and that Excel can update its links. Do not replace a valid external reference with a guessed file path: an incorrect link can create a different problem.

A practical troubleshooting sequence

Use this order to isolate the cause without rewriting a working calculation unnecessarily:

Select the cell and read the complete error. Confirm that it is #VALUE!, not a zero or another error type.
Inspect the formula arguments and identify the sum range and every criteria range.
Compare each range’s first and last rows and columns. Make the dimensions identical and align the records row by row.
Check for error values in the referenced data and in cells used as criteria.
If the formula uses another workbook, open that file and test whether the error clears.
Recheck the result against a few rows that should qualify. Confirm that the total is plausible, not just free of an error.

In Microsoft 365 for Windows, select the formula cell and use Formulas > Evaluate Formula to step through how Excel evaluates the formula. The Formulas tab also includes Trace Precedents, which can help show which cells feed the selected calculation. On Mac, the Formula tab has formula-auditing tools too, though the available controls and their placement can differ by Excel release. Excel for the web has formula editing and error indicators, but its ribbon layout is not identical to the Windows desktop app.

When to use a different approach

If the ranges are correct but the calculation is becoming hard to maintain, consider organizing the data as an Excel table. Structured references can expand as rows are added, reducing the chance that a manually fixed range leaves out new records. For many multi-condition totals, however, SUMIFS remains a clear and appropriate choice; switching functions will not fix misaligned source data.

A PivotTable can be easier when the goal is to summarize many categories, compare several fields, or let other people filter the results interactively. Use SUMIFS when a worksheet needs a formula-driven total in a particular cell or report layout. For more complex logic that includes OR conditions or calculations over arrays, other formulas or a PivotTable may fit better, but first confirm that the original error is not simply a range-size mismatch.

Frequently Asked Questions

Why does SUMIFS return #VALUE! when the ranges look the same?
Check exact endpoints and dimensions, not just the visible portion of the worksheet. One range may include an extra row or column, or begin on a different row. Also check for errors in referenced cells and test whether an external source workbook being closed is involved.

Does SUMIFS return #VALUE! when there are no matches?
No. With valid arguments and no matching records, SUMIFS normally returns 0. A #VALUE! result points to an evaluation problem, such as incompatible range dimensions or an error in a referenced value.

Can SUMIFS use whole-column ranges?
Yes. Whole-column references such as A:A and B:B have matching dimensions when used consistently. They can be convenient, though they may make calculations slower in large or formula-heavy workbooks. If a formula uses an external workbook, test the link with the source open as well.

Why does the formula work when the other workbook is open?
Excel can behave differently with some SUMIFS formulas that use external references when the source workbook is closed. If opening the source clears the error, keep the source accessible, bring the data into the current workbook, or review how the link is maintained.

What is the difference between SUMIF and SUMIFS?
SUMIF handles one condition and places its criteria range before the sum range. SUMIFS supports multiple conditions and places the sum range first. Because their argument orders differ, changing one function name to the other without rearranging arguments can produce a wrong result.

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.