Fix Excel SUMIFS Returning Zero and Running Slowly in Microsoft 365
Question details
The user is experiencing performance issues and incorrect results where the SUMIFS function returns zero and calculates very slowly in Microsoft 365, despite the same workbook functioning correctly in Excel 2016.
- Product
- Microsoft Excel 365
- Device & OS
- not provided
- Scenario
- Calculating data using the SUMIFS formula in a spreadsheet within Microsoft 365.
- Observed behavior
- The SUMIFS formula returns a value of zero instead of the correct sum and causes the Excel application to run slowly during calculations.
Before proceeding with troubleshooting, test whether this formula issue occurs in all Excel workbooks or just one specific file to determine if the spreadsheet itself is corrupted.
Open Excel in Safe Mode and Disable COM Add-ins
Running Excel in Safe Mode bypasses third-party add-ins that might be conflicting with Microsoft 365's calculation engine and causing slow performance.
Sometimes, COM add-ins that worked perfectly in older versions like Excel 2016 become incompatible with Microsoft 365. Disabling them can instantly restore calculation speeds and fix formula glitches.
Press the Windows Key + R to open the Run dialog box. Type 'excel /safe' and press Enter to launch Microsoft Excel in Safe Mode.
Open your problematic workbook. If the SUMIFS formula calculates quickly and accurately, an add-in is likely the culprit.
Go to the 'File' tab, select 'Options', and then click on 'Add-ins' in the left sidebar.
At the bottom of the window, change the 'Manage' dropdown to 'COM Add-ins' and click 'Go'. Uncheck all add-ins in the list and click 'OK', then restart Excel normally to see if the issue is resolved.
Verify SUMIFS Text Criteria Syntax
Ensure that your text criteria within the SUMIFS formula are correctly formatted, as missing quotation marks will cause the function to fail and return zero.
Perform an Online Repair of Microsoft 365
If the application files are corrupted, an online repair will scan and replace damaged Microsoft 365 components that may be impeding formula calculations.
Try WPS Office for Fast and Accurate Formula Calculations
If Microsoft 365 continues to struggle with slow calculations and formula errors after updates, consider switching to WPS Office. It is a highly compatible, lightweight, and free alternative that processes complex formulas like SUMIFS quickly without freezing or lagging.
Frequently Asked Questions
Why does SUMIFS return zero when there is clearly matching data in my sheet?
This usually happens if the data types do not match (e.g., numbers stored as text in the criteria range), if there are hidden trailing spaces in your cells, or if text criteria in your formula are not enclosed in double quotation marks.
Can referencing large ranges in SUMIFS cause Excel to run slowly?
Yes. Referencing entire columns (such as A:A) forces Excel to evaluate over a million rows per condition. To improve calculation speed, restrict your formula ranges to the exact rows containing your data (e.g., A1:A5000).
Why does my workbook calculate perfectly in Excel 2016 but fail in Microsoft 365?
Microsoft 365 utilizes an updated calculation engine that includes dynamic arrays. Occasionally, older COM add-ins conflict with this new engine, or syntax that was loosely tolerated in 2016 is handled strictly in 365, causing unexpected zeroes or lag.
How can I force Excel to recalculate a formula that seems stuck?
You can force a manual recalculation by pressing the F9 key on your keyboard, or by navigating to the Formulas tab on the ribbon and clicking the 'Calculate Now' button.
