#Value-error from SUMPRODUCT

[AndreasHoffmann2]

Please have a look at the following code:

ExcelPackage p1 = new ExcelPackage(); var sheet = p1.Workbook.Worksheets.Add("Test"); sheet.Cells["A1"].Value = 1; sheet.Cells["B1"].Value = 2; sheet.Cells["A2"].Value = 3; sheet.Cells["B2"].Value = 4; var formula = "SUMPRODUCT((A1:A2)*(B1:B2))"; sheet.Cells["A3"].Formula = formula; sheet.Calculate(); var x = sheet.Calculate(formula);`

After executing it, "x" contains a #VALUE-Error (NullReferenceException). When I save the spreadsheet directly to disc, it opens just fine.

When opening it in protected mode (e.g. after downloading), A3 does contain the #VALUE-Error as well. Only after disabling protected mode, Excel calculates the correct result.

Versions used: EPPLUS: 4.5.3.2 .NET-Framework: 4.7.1

[swmal]

Hi Andreas,

apologies for a very late reply. The formula calc engine in EPPlus 4 does not support the (A1:A2)*(B1:B2) piece. The result of that expression would "spill" over into an array, which is not implemented and listed as a gap in the Formula calculation documentation in the wiki. This might be something we will look into in the next major version.

Best regards,

Mats