Fix SUMIF or SUMIFS #VALUE! Errors After a Microsoft 365 Update
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.

- 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 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.
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.
Click the cell that contains the SUMIF or SUMIFS formula and look at the formula bar.
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.
Press F2 or click in the formula bar, correct any mismatched range, and press Enter to recalculate.
Verify that the cell now returns the expected numeric total instead of #VALUE!.

Test SUMIF or SUMIFS with Ranges on the Same Worksheet
Use this when the error appears only for cross-sheet references; isolating the ranges helps identify whether a cross-sheet reference is causing the issue.
Use SUMPRODUCT as a Workaround and Update Microsoft 365
Use this if the #VALUE! error persists after correcting ranges; SUMPRODUCT avoids the specific SUMIF/SUMIFS update issue.
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.

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.




