SUM() behaviour with boolean parameters

[sgoodgrove]

trafficstars

Hi,

I've identified a discrepancy between how Excel behaves when the SUM() function is given a series of boolean predicates and how EPPlus handles the same expression.

For example, given a sheet with the text "Yes" in cells A1 and A2, then the expression =SUM(A1="Yes",A2="Yes", A3="Yes") Excel will return 2 and EPPlus returns 0.

[Test]
public void Test1()
{
    var package = new ExcelPackage();
    var sheet1 = package.Workbook.Worksheets.Add("Sheet1");

    sheet1.Cells["A1"].Value = "Yes";
    sheet1.Cells["A2"].Value = "Yes";
    sheet1.Cells["A3"].Value = "";

    sheet1.Cells["A4"].Formula = "=SUM(A1=\"Yes\",A2=\"Yes\", A3=\"Yes\")";

    var a4 = sheet1.Cells["A4"];
    a4.Calculate();

    // Excel returns 2, EPPlus returns 0.
    Assert.That(a4.Value, Is.EqualTo(2)); 
}

It is worth noting that performing a SUM() over a named range that contains only boolean values (TRUE/FALSE) returns 0 in both EPPlus and Excel, it is only this case where the values are individual parameters that Excel treats them as 1 and 0 and sums them up.

[swmal]

Hello,

thanks for reporting this.

While your formula/values indeed returns 2, this...

SUM(A1:A3="Yes")

...and this...

A1 formula = TRUE()
A2 formula = TRUE()
A3 formula = FALSE()

SUM(A1, A2, A3)

...both returns 0 in Excel.

While this - applied on A1 in your example - will return 1:

SUM(IF(A1="Yes";TRUE();FALSE()))

So the logic seems to be that individual boolean parameters that originates from an evaluation are treated as 1/0 rather than booleans. We will investigate this further and will adjust the behaviour in a coming version.