Problem with COUNTIFS treating 0 incorrectly

Aspose.Cells Product Family
1

The formula below should include cells with a value of 0 like Excel does, but it ignores them:
=COUNTIFS(A1:A3,"<0.5",A1:A3,">-0.5")
And this formula does the opposite, it includes zeros this time when it should ignore them:
=COUNTIF(A1:A3,"<=-0.3")
To test this add these scores to the top of the first column:
0
0.3
-0.3
Then Excel gives counts of 3 and 1 but Aspose gives 2 and 2.
I set up the sheet as above in Excel, then calculated and saved with this C# code to get the wrong counts.
var workbook = new Workbook(@“c:\temp\AsposeCountIfBug.xlsx”);
workbook.CalculateFormula();
workbook.Save(@“c:\temp\AsposeCountIfBugOut.xlsx”);
Thank you Aspose for a great product, I was surprised by this one small error.

2

@catnoise,

Thanks for the details.

Please notice, I am able to reproduce the issue as you mentioned by using the following simplest lines of code with a template file (I inserted your devised values into A1:A3 cells in the first worksheet and saved the file manually in MS Excel). I found the issue with COUNTIFS formula/function that treats 0 incorrectly:
e.g.
Sample code:

            var workbook = new Workbook("e:\\test2\\Bk_countif1.xlsx");
            Console.WriteLine(workbook.Worksheets[0].Cells["D5"].Value);//3
            Console.WriteLine(workbook.Worksheets[0].Cells["D6"].Value);//1
            workbook.CalculateFormula();
            Console.WriteLine(workbook.Worksheets[0].Cells["D5"].Value);//2
            Console.WriteLine(workbook.Worksheets[0].Cells["D6"].Value);//2

I have logged a ticket with an id “CELLSNET-51268” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

3

@catnoise,

We are pleased to inform you that your issue has been resolved. The fix will be included in the (upcoming) release (Aspose.Cells for .NET v22.6), which is scheduled to be published before the end of this week or in the next week early. You will also be notified when the next version is released.

4

The issues you have found earlier (filed as CELLSNET-51268) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi

5

The issues you have found earlier have been fixed in this update. If you have downloaded the prior one(22.6.0), please use this update to replace the downloaded one because 22.6 has a bug which sometimes may cause generated file corrupted.

6

Thank you for looking at this so quickly, I have upgraded to nuget 22.6.1 and can see the first formula now gives the right values, but the second is still broken.
=COUNTIF(A1:A3,"<=-0.3")
This should give 1 but is still giving 2 when the data sheet has 0, 0.3, -0.3 as before. I hope this does not give you too much trouble finding the error.

7

@catnoise,

You are right as I confirmed using the newer version. The second formula is still not giving correct results. I have reopened the issue again. We will look into it soon.

8

@catnoise,

This is to inform you that your issue has been resolved precisely now. The fix will be included in the (upcoming) release (Aspose.Cells for .NET v22.7), which is scheduled to be published in the second week of July 2022. You will also be notified when the next version is released.