Calculating SUMPRODUCT(SUMIFS...) function failed in .NET

Aspose.Cells Product Family
1

Hello,

We’ve encountered an issue with a SUMPRODUCT(SUMIFS…) function which does not seems to be calculated by Aspose.

I’ve attached a zip archive containing a sample console application describing the situation, the formula should produce 2500 but it produces a NULL value instead !

The formula being tested has this form: =SUMPRODUCT(SUMIFS(Database!B:B,Database!$A:$A,$D1:$Y1)) . I’ve also attached TestFilex.xlsx which matches the one created in the sample app.

We are using Aspose.Cells version 7.7.2.0.

Any feedback will be highly appreaciated.

Thank you,
Mihai Andrei
Sr Software Engineer
IBM Romania

2

Hi Mihai,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue. Aspose.Cells is unable to calculate the SUMPRODUCT(SUMIFS…) formula and returns null. When the file is directly loaded, then CalculateFormula() throws exception.

We have logged this issue in our database. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-42462.

C#


Workbook workbook = new Workbook();


var databaseSheet = workbook.Worksheets.Add(“Database”);

databaseSheet.Cells[“A1”].Value = “60001”;

databaseSheet.Cells[“B1”].Value = “1000”;

databaseSheet.Cells[“A2”].Value = “60002”;

databaseSheet.Cells[“B2”].Value = “1500”;


var displaySheet = workbook.Worksheets.Add(“Display”);

displaySheet.Cells[“D1”].Value = “60001”;

displaySheet.Cells[“E1”].Value = “60002”;


displaySheet.Cells[“A1”].Formula = “=SUMPRODUCT(SUMIFS(Database!B:B,Database!$A:$A,$D1:$Y1))”;


workbook.CalculateFormula(true);


object a1Value = displaySheet.Cells[“A1”].Value;

Console.WriteLine(“A1 Cell Value: ‘{0}’”, a1Value == null ? “NULL” : a1Value.ToString());

3

Hi,

Thanks for using Aspose.Cells.

Please download and try the fix: Aspose.Cells
for .NET v7.7.2.6 and let us know your feedback.

4

Hello,

I’ve updated the test console app to use the 7.7.2.6 version, as instructed, but the computed value by Aspose is 0, instead of the 2500 as Excel does.

I’ve updated the test app to use numeric values for the SUMPRODUCT function, and also to save the resulted file - see attached TestFile.xlsx file.

Please advise.

Thank you,
Mihai Andrei

5

Hi Mihai,

Thanks for your feedback and using Aspose.Cells.

We found that Aspose.Cells calculates the formula 0 while MS-Excel calculates it as 2500. We need to investigate this issue further. We have logged your comments in our database against this issue and reopened it for a fix. Once, it is resolved or we have some other update for you, we will let you know asap.

Screenshot:

6

Hi,

Thanks for using Aspose.Cells.

Please change your code as following.

C#


Workbook workbook = new Workbook(FileFormatType.Xlsx);


var databaseSheet = workbook.Worksheets.Add(“Database”);

databaseSheet.Cells[“A1”].Value = 60001;

databaseSheet.Cells[“B1”].Value = 1000;

databaseSheet.Cells[“A2”].Value = 60002;

databaseSheet.Cells[“B2”].Value = 1500;


var displaySheet = workbook.Worksheets.Add(“Display”);

displaySheet.Cells[“D1”].Value = 60001;

displaySheet.Cells[“E1”].Value = 60002;


displaySheet.Cells[“A1”].Formula = “=SUMPRODUCT(SUMIFS(Database!B:B,Database!$A:$A,$D1:$Y1))”;

7

Hello,

Thanks for the work-around. We’ve successfully tested it against 7.7.2.6 and also the just released 8.0.0.0 public version.

Thank you,
Mihai Andrei

8

Hi,


Good to know that it figures out your issue now. Also, our latest versions work well for your needs/ requirements. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.