Posted 6 October 2026, 4:46 am EST - Updated 6 October 2026, 4:54 am EST
Hello,
We found a difference between SpreadJS and Excel in the criteria functions (COUNTIF, COUNTIFS, SUMIF, SUMIFS). It happens when the criterion looks like a year and month, for example “>=2025-01”.
Excel keeps “2025-01” as text and compares it with the text cells as a string. SpreadJS converts it to a date (2025-01-01), which gives wrong results in two ways:
- With ExcelCompatibleCalcMode = true, text cells are never matched by a comparison criterion, so COUNTIFS and SUMIFS return 0.
- In both modes, cells that hold real dates are matched by the criterion, while Excel matches none of them.
With ExcelCompatibleCalcMode = false (the default, also used by the Designer), the text cells are converted to dates too. Because “yyyy-mm” keys sort in the same order as their dates, the text case gives the Excel result by chance. That is why the problem does not show when the file is opened in the Designer.
Environment
- SpreadJS 19.2.3. The results are the same with the en-US and fr-FR cultures.
- Reference: Excel in French (fr-FR).
- The attached file test_excel_compatiblity.xlsx contains the values calculated and saved by Excel.
Test data
Column A holds text: the keys are entered as =“2025-01”, and ISTEXT(A2) returns TRUE in Excel. Column E holds real dates.
Row A (text) B (amount) E (date)
1 2024-12 1 2024-12-15
2 2025-01 10 2025-01-15
3 2025-05 100 2025-05-15
4 2025-09 1000 2024-12-18
5 2025-10 10000 2024-12-19Formulas
D1 =COUNTIFS(A1:A5,">=2025-01")
D2 =SUMIFS(B1:B5,A1:A5,">=2025-01",A1:A5,"<=2025-09")
D3 =SUMIFS(B1:B5,A1:A5,">="&2025&"-01",A1:A5,"<="&2025&"-"&"09")
D4 =COUNTIFS(A1:A5,"2025-01")
D5 =COUNTIFS(E1:E3,">=2025-01")D3 builds the same criteria as D2 by concatenation, so the conversion happens when the criterion is evaluated, not when the formula is parsed.
Results
“Mode off” means ExcelCompatibleCalcMode = false, “mode on” means ExcelCompatibleCalcMode = true.
Cell Excel SpreadJS mode off SpreadJS mode on
D1 4 4 0 <- wrong
D2 1110 1110 0 <- wrong
D3 1110 1110 0 <- wrong
D4 1 1 1
D5 0 2 <- wrong 2 <- wrongThe operators disagree with each other
With the mode on, the “=” criterion compares the operand as text, but the other operators compare it as a date:
Formula Mode on Mode off
=COUNTIF(A1:A5,"2025-01") 1 1
=COUNTIF(A1:A5,"<>2025-01") 5 4
=COUNTIF(A1:A5,"<2025-01") 0 1With the mode on, “=” and “<>” together count 6 cells in a range of 5 cells.
Cases that work
These cases give the expected result in both modes, so the problem is limited to text criteria that look like a date:
- =COUNTIF(E1:E3,“>=”&DATE(2025,1,1)) returns 2: a date criterion on date cells.
- =COUNTIF(F1:F3,“>=b”) with “a”, “b”, “c” in F1:F3 returns 2: a text criterion that does not look like a date.
- =A2>=“2025-01” returns TRUE: a direct comparison keeps the operand as text.
Expected behavior
Criteria should be parsed as Excel parses them. When Excel keeps the operand as text, SpreadJS should compare it with the text cells as a string, and never match number or date cells against it. With the data above, D1:D5 should return 4, 1110, 1110, 1, 0 in both modes.
Why turning the mode off is not a workaround
- We need ExcelCompatibleCalcMode = true for other Excel behaviors, for example ISNUMBER, and SUM over ranges that hold numbers stored as text.
- With the mode off, D5 is still wrong.
- Our customers’ workbooks use “yyyy-mm” period keys stored as text and add them up with SUMIFS. In one production workbook, hundreds of SUMIFS cells return 0 with the mode on.
Code to reproduce
// SpreadJS 19.2.3, default culture
const spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
const sheet = spread.getActiveSheet();
// A1:A5: text keys "yyyy-mm", B1:B5: amounts
["2024-12", "2025-01", "2025-05", "2025-09", "2025-10"].forEach((key, i) => {
sheet.setValue(i, 0, key);
sheet.setValue(i, 1, Math.pow(10, i));
});
// E1:E3: real dates
[new Date(2024, 11, 15), new Date(2025, 0, 15), new Date(2025, 4, 15)].forEach((date, i) => {
sheet.setValue(i, 4, date);
sheet.setFormatter(i, 4, "yyyy-mm-dd");
});
sheet.setFormula(0, 3, 'COUNTIFS(A1:A5,">=2025-01")');
sheet.setFormula(1, 3, 'SUMIFS(B1:B5,A1:A5,">=2025-01",A1:A5,"<=2025-09")');
sheet.setFormula(2, 3, 'SUMIFS(B1:B5,A1:A5,">="&2025&"-01",A1:A5,"<="&2025&"-"&"09")');
sheet.setFormula(3, 3, 'COUNTIFS(A1:A5,"2025-01")');
sheet.setFormula(4, 3, 'COUNTIFS(E1:E3,">=2025-01")');
function show(excelCompatible) {
GC.Spread.CalcEngine.ExcelCompatibleCalcMode = excelCompatible;
spread.calculate(GC.Spread.Sheets.CalculationType.all);
console.log(excelCompatible, [0, 1, 2, 3, 4].map((row) => sheet.getValue(row, 3)));
}
show(false); // logs [4, 1110, 1110, 1, 2]
show(true); // logs [0, 0, 0, 1, 2]
// Excel gives [4, 1110, 1110, 1, 0]You can also open the attached file in the SpreadJS Designer. When the file is opened, the Designer shows the values saved by Excel (D5 = 0). After a cell in E1:E3 is edited, D5 is recalculated and shows 2.
Questions
- Can you confirm this is a bug, and tell us in which version it will be fixed?
- Is there a supported way to keep a criterion such as “>=2025-01” as text?
Thank you for your help.test_excel_compatiblity.zip
