Formula Errors

Fix Excel COUNTIFS and SUMIFS Returning Zero in Teams or SharePoint

Phi Hung VoPhi Hung Vo Nov 18, 2026 868 views

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.

Fix Excel COUNTIFS and SUMIFS Returning Zero in 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.
Before you start

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.

Solution 1Recommended

Replace text dates with real Excel dates

Use this when date criteria or source cells may contain text, particularly dates entered with periods.

1
Test a date cell

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.

2
Convert text dates

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.

3
Use an unambiguous criterion

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.

4
Save and test online

Save the workbook, reopen it from Teams or SharePoint, and compare the formula results with desktop Excel.

Replace text dates with real Excel dates
Date display is not date conversion: Changing a cell's display format alone does not convert text into a date. A slash-formatted entry such as 02/01/2020 can mean different dates in different locales, so confirm the intended date before entering it.
Free Microsoft Office alternative

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.

Work with COUNTIFS and SUMIFS formulas in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX filesUse a familiar interface for a smoother move from OfficeAccess a free, lightweight office suite
microsoft office alternative - wps office

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.