Different COUNTIF behavior from Excel

[Derrick Gunter]

This works differently in XlsIO and Excel:

=COUNTIF(B1:B4, "1, 2")

In Excel it counts the cell if it has exactly "1, 2".

In XlsIO it counts the cell if it has either "1" or " 2".

Tested with Syncfusion.XlsIO.Net.Core version 24.1.41

Anyone know why the difference in behavior?

(cross posted from Blazor forum)

[Karthika Santhana Krishnan]

Hi Derrick,

 

We have attached a working sample that addresses the reported issue. Please review it and let us know if the issue resolved on your end. If issue still persists, please modify the attached sample to reproduce the issue and share it back with us for further investigation.

 

Regards,

Karthika

Attachment: CountIFFormulaWinforms_ef878977.zip

[Derrick Gunter]

Thanks for the quick reply. That works. However, we typically read the formulas from a spreadsheet (sample attached). When we do this, the code below works differently than if the COUNTIF formula is executed in Excel directly. How do we ensure that COUNTIF works the same in XlsIO and Excel?

public void TestCountIfFormula1()
    {
      using(ExcelEngine excelEngine =new ExcelEngine())
      {
        //Instantiate the Excel application object
        IApplication application = excelEngine.Excel;

        //Set the default application version
        application.DefaultVersion = ExcelVersion.Excel2016;

        //Load the existing Excel workbook into IWorkbook
        using(Stream inputStream = newMemoryStream(File.ReadAllBytes("test.xlsx")))
        {
          IWorkbook workbook = application.Workbooks.Open(inputStream);
          var sheet = workbook.Worksheets[0];

          sheet.Range["B1"].Text="1, 2";
          sheet.Range["B2"].Text="1";
          sheet.Range["B3"].Text=" 2";
          sheet.Range["B4"].Text="1, 2";
          sheet.Range["B5"].Text="3";
          sheet.Range["B6"].Text="1, 2";

          sheet.EnableSheetCalculations();
          var result = sheet.Range["C1"].CalculatedValue;
          Console.WriteLine(result);

          Assert.AreEqual("3", result);
        }
      }
    }

Attachment: test_785b05d5.xlsx

[Karthika Santhana Krishnan]

Hi Derrick,

 

We have reproduced the issue on our end. 

We are currently validating it. We will share the validation details on October 14, 2025.

 

Regards,

Karthika

[Karthika Santhana Krishnan]

Hi Derrick,

 

We have checked and confirmed the reported issue "COUNTIF Formula Returns Incorrect Result When Criteria Contains Text and ParseArgumentSeparator in CalcEngine" as a defect. We have logged a bug, and it will be included in NuGet release after the SP1 rollout, which is scheduled for October 28th, 2025.

 

Please use the below feedback link to track the status of the reported bug.

COUNTIF Formula Returns Incorrect Result When Criteria Contains Text and ParseArgumentSeparator in CalcEngine in WinForms | Feedback Portal

 

We will let you know once it is released. We appreciate your patience until then.

 

Disclaimer: Inclusion of this solution in the weekly release may change due to other factors including but not limited to QA checks and works reprioritization.

 

Regards,

Karthika

[morven anselm]

Interesting finding! It seems XlsIO interprets multiple criteria separated by commas differently than Excel. Maybe Syncfusion treats commas as OR conditions. Hope they align this behavior soon! Melon Playground

[Santip Raja Palanisamy]

Hi Morven,

The XlsIO library follows the behavior of Microsoft Excel, including the user culture’s list/argument separators. The difference you noticed with multiple criteria separated by commas is due to a confirmed issue in this specific function (COUNTIF with text criteria and argument-separator handling). We have logged it, and the fix is planned for the NuGet release following the SP1 rollout targeted for October 28, 2025.

Regard,

Santip Raja.

[Gowtham Ponraj]

Hi Morven,

We sincerely apologize for the inconvenience. Unfortunately, the fix for the issue related to "COUNTIF formula returns incorrect result when criteria contains text and parseArgumentSeparator in CalcEngine" was not included in today’s NuGet package release as promised.

While the issue has been addressed and a fix implemented, we encountered some issues during the testing phase, which prevented its inclusion in the release. These testing issues have now been resolved, and the fix will be included in our upcoming weekly NuGet release, scheduled for November 4, 2025.

In the meantime, if you would like to proceed with early validation, we can provide a custom NuGet package containing the fix. If you’re interested, please confirm the Syncfusion version you are currently using so we can prepare the patch. 

We appreciate your patience during this time. Thank you for your understanding and cooperation.

Regards,
Gowtham.

[Gowtham Ponraj]

Hi Morven,

We are glad to announce that our weekly patch release (31.2.5) is rolled out. We have included the fix for the reported “COUNTIF Formula Returns Incorrect Result When Criteria Contains Text and ParseArgumentSeparator in CalcEngine” issue in this release. So, kindly upgrade your package version to the latest to avail of these changes (31.2.5).

Package Link: NuGet Gallery | Syncfusion.XlsIO.WinForms 31.2.5

Feedback link: COUNTIF Formula Returns Incorrect Result When Criteria Contains Text and ParseArgumentSeparator in …

For your convenience, we’ve also attached the sample referring the latest packages for your reference.

Issue Root Cause:
While calculating the COUNTIF formula, the criteria argument was incorrectly split by a comma even when it was plain text rather than an array, resulting in an inaccurate CalculatedValue for cell C1. This has been resolved by adding a condition to ensure that splitting by comma only occurs when the criteria is explicitly in array format. With this fix, the formula now returns the correct CalculatedValue.

We thank you for your support and appreciate your patience in waiting for this release. Please get in touch with us if you require any further assistance.

Regards,
Gowtham.

Attachment: CountIFFormulaWinforms_Modified_494f3a80.zip