Circular reference exception sometimes thrown incorrectly for SUMIF, COUNTIFS

[keithclarkj]

trafficstars

https://github.com/EPPlusSoftware/EPPlus/issues/635 Previous issue (see 635 above), previously fixed in v6.0.4 and v5.8.10 - we have upgraded EPPlus to v7.0.5 and this issue has cropped up again.

Unit tests included below:

[TestMethod]
public void SumIf_SumThisRowWithoutCircularReferences()
{
    using (var pck = new ExcelPackage())
    {
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        sheet1.Cells["A1"].Value = "SumResult";
        // This shouldn't be a circular reference, because the 1:1="SUMMABLE" condition should filter out A2
        sheet1.Cells["A2"].Formula = "SUMIF(1:1,\"SUMMABLE\",2:2)";
        
        sheet1.Cells["B2"].Value = 1;
        sheet1.Cells["C2"].Value = 2;
        sheet1.Cells["D2"].Value = 3;
        sheet1.Cells["E2"].Value = 4;
        sheet1.Cells["F2"].Value = 5;
        sheet1.Cells["G2"].Value = 6;

        sheet1.Cells["C1"].Value = "SUMMABLE";
        sheet1.Cells["D1"].Value = "SUMMABLE";
        sheet1.Cells["E1"].Value = "SUMMABLE";
        sheet1.Cells["G1"].Value = "SUMMABLE";

        pck.Workbook.Calculate();

        Assert.AreEqual(15, sheet1.Cells["A2"].GetValue<double>(), Tolerance);
    }
}
[TestMethod]
public void CountIfs_CountThisRowWithoutCircularReferences()
{
    using (var pck = new ExcelPackage())
    {
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        sheet1.Cells["A1"].Value = "SumResult";
        // This shouldn't be a circular reference, because the 1:1="COUNTABLE" condition should filter out A2 before the 2:2 filter is applied
        sheet1.Cells["A2"].Formula = "COUNTIFS(1:1,\"COUNTABLE\",2:2,\"<>\")";

        sheet1.Cells["B2"].Value = 1;
        sheet1.Cells["C2"].Value = 2;
        sheet1.Cells["E2"].Value = 4;
        sheet1.Cells["F2"].Value = 5;
        sheet1.Cells["G2"].Value = 6;

        sheet1.Cells["C1"].Value = "COUNTABLE";
        sheet1.Cells["D1"].Value = "COUNTABLE";
        sheet1.Cells["E1"].Value = "COUNTABLE";
        sheet1.Cells["G1"].Value = "COUNTABLE";

        pck.Workbook.Calculate();

        Assert.AreEqual(3, sheet1.Cells["A2"].GetValue<double>(), Tolerance);
    }
}

[swmal]

Thanks for reporting, we'll have a look at this.

[swmal]

Both of these tests are added to our test suite since this issue was reported and passes in EPPlus 7. You need to set the AllowCircularReferences flag to true when calling Calculate()

https://github.com/EPPlusSoftware/EPPlus/blob/develop7/src/EPPlusTest/FormulaParsing/Excel/Functions/CountIfsTests.cs#L197 https://github.com/EPPlusSoftware/EPPlus/blob/develop7/src/EPPlusTest/FormulaParsing/Excel/Functions/SumIfTests.cs#L43

As you can see in these tests, what we have added is this:

pck.Workbook.Calculate(x => x.AllowCircularReferences = true);

You can also set the AllowCircularReferences flag via config files, see this page

I will add an enhancement tag to this issue and we will investigate if we can solve this in a better way since the circular reference should be checked after the filter has been applied (as you wrote).