Fix Excel COUNTIFS and SUMIFS Returning Zero in Teams or SharePoint
Question details
The user needs COUNTIFS and SUMIFS formulas to return consistent results in desktop Excel and when the workbook is opened from Teams or SharePoint.

- Product
- Microsoft Excel and Excel for the web
- Device & OS
- not provided
- Scenario
- A workbook with date-based COUNTIFS and SUMIFS formulas is opened through Teams or SharePoint.
- Observed behavior
- The formulas work in desktop Excel but return zero online. Re-entering them temporarily restores the expected results.
Identify the date cells and criteria used by the affected formulas, and confirm whether each date means day/month/year or month/day/year. Open the workbook in desktop Excel and Excel for the web so you can compare the results.
Replace text dates with real Excel dates
Use this when date criteria or source cells may contain text, particularly dates entered with periods.
In an empty cell, enter =ISNUMBER(B6), replacing B6 with a date cell used by the formula. TRUE indicates an Excel numeric value; FALSE indicates that the entry is not a real Excel date.
In desktop Excel, select the affected date column and choose Data > Text to Columns. Follow the wizard, select Date and the matching DMY or MDY order, and click Finish. Check that the converted dates represent the intended days.
Replace a text date in the formula with a DATE expression. For example, =COUNTIFS(A2:A100,">="&DATE(2020,1,2)) counts dates on or after January 2, 2020. Use the equivalent DATE expression in SUMIFS criteria, adjusting argument separators if your Excel locale requires semicolons.
Save the workbook, reopen it from Teams or SharePoint, and compare the formula results with desktop Excel.

Check regional settings and workbook calculation
Use this if date values are valid but results still differ between desktop Excel and Excel for the web.
Work with Date-Based Formulas in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel workbooks in WPS Spreadsheets for an easier transition, though WPS does not fix regional settings in Microsoft Teams or SharePoint.

Frequently Asked Questions
Why does my formula work in desktop Excel but return zero online?
A date stored as text or interpreted under a different regional format can prevent criteria from matching. Test the date cells with =ISNUMBER(B6), and check the workbook's date criteria and SharePoint locale.
How can I avoid ambiguous dates in COUNTIFS or SUMIFS?
Use DATE in the criterion, such as ">="&DATE(2020,1,2), instead of typing a date as text. This example represents January 2, 2020.
Will changing the date format convert text into a real date?
No. In desktop Excel, select the affected cells and use Data > Text to Columns, choosing the date order that matches the original entries. Verify the converted dates afterward.
Where do I check the SharePoint locale?
On the site storing the workbook, open Settings > Site information > View all site settings > Regional settings. You may need administrator permissions to change the locale, and changes can take time to propagate.




