Formula Errors

Fix SUMIF or SUMIFS #VALUE! Errors After a Microsoft 365 Update

Phi Hung VoPhi Hung Vo Nov 13, 2026 868 views

Question details

The user needs to resolve SUMIF or SUMIFS formulas returning #VALUE! or showing a range reference instead of calculated results after a Microsoft 365 or Office update, especially when criteria and sum ranges are on different worksheets.

Fix SUMIF or SUMIFS #VALUE! Errors After a Microsoft 365 Update
Product
Microsoft Excel (Microsoft 365/Office)
Device & OS
not provided
Scenario
A user has a workbook with SUMIF or SUMIFS formulas that stopped calculating correctly after an Office update, using criteria and sum ranges across different worksheets.
Observed behavior
Formulas return #VALUE! or display a range reference instead of the expected numeric result.
Before you start

Before changing formulas, note the exact Microsoft 365 version and build where the error occurs, and confirm whether the criteria range and sum range are on the same worksheet or different worksheets.

Solution 1Recommended

Verify SUMIF and SUMIFS Criteria and Sum Ranges Use Identical Dimensions

Use this first when a formula stops calculating after an update; mismatched range sizes are a common cause of #VALUE! errors.

SUMIF and SUMIFS expect criteria_range and sum_range to match in size and shape. If the ranges point to whole columns or different-sized areas, Excel may return #VALUE! or show the formula result incorrectly, especially cross-sheet.

1
Select the formula cell

Click the cell that contains the SUMIF or SUMIFS formula and look at the formula bar.

2
Compare range dimensions

In the formula bar, check that the criteria_range and sum_range arguments cover the same number of rows and columns, such as A2:A100 and B2:B100.

3
Edit the ranges

Press F2 or click in the formula bar, correct any mismatched range, and press Enter to recalculate.

4
Confirm the result

Verify that the cell now returns the expected numeric total instead of #VALUE!.

Verify SUMIF and SUMIFS Criteria and Sum Ranges Use Identical Dimensions
Ranges matched: SUMIF and SUMIFS work reliably when every criteria range and the sum range have identical dimensions.
Free Microsoft Office alternative

Open and Calculate SUMIF and SUMIFS Workbooks Without the Update Bug in WPS Spreadsheets

WPS Office is a free, lightweight Microsoft Office alternative that opens Excel workbooks, preserves formulas, and gives you a familiar spreadsheet interface—so you can continue using SUMIF and SUMIFS without waiting for the Microsoft 365 bug fix.

Free to download and use for everyday spreadsheet workOpens and saves .xlsx, .xls, and other Microsoft Excel formats with high compatibilityFamiliar ribbon interface makes migration from Microsoft Excel easyHandles SUMIF, SUMIFS, SUMPRODUCT, and cross-sheet formulas in WPS SpreadsheetsLightweight and fast, with no need to wait for a Microsoft 365 update
microsoft office alternative - wps office

Frequently Asked Questions

Why does SUMIF return #VALUE! after an Office update?

An Office update may change how Excel handles certain cross-sheet range references. First verify that the criteria range and sum range have identical dimensions and use fully qualified sheet names, or test the formula with all ranges on the same worksheet.

Can I use SUMIFS with ranges on different worksheets?

Yes, SUMIFS can reference different worksheets if the references are valid and the ranges match in size. However, some Microsoft 365 builds may fail with cross-sheet SUMIFS; use a same-sheet test or switch to SUMPRODUCT as a workaround.

How do I install the latest Microsoft 365 build?

Open Excel, click File > Account > Update Options > Update Now, and allow the update to complete. Restart Excel and recalculate the workbook.

Can WPS Office open a workbook with SUMIF and SUMIFS formulas?

Yes, WPS Spreadsheets supports SUMIF and SUMIFS formulas and can open and save Microsoft Excel files including .xlsx and .xls formats.

What is a SUMPRODUCT alternative for SUMIFS?

You can use a formula such as =SUMPRODUCT((criteria_range=criteria)*sum_range). Make sure the ranges are the same size and avoid whole-column references if possible.