Why is the Excel formula not formulating?

Question

I'm creating a spreadsheet with EPPlus. I am trying to get a formula to calculate a sum of values with this code:

using (var totalOccurrencesCell = priceComplianceWorksheet.Cells[rowToPopulate, 2])
{
    totalOccurrencesCell.Style.Font.Size = DATA_FONT_SIZE;
    totalOccurrencesCell.Style.Numberformat.Format = NUMBER_FORMAT_THOUSANDS;
    if (rowToPopulate <= SUMMARY_HEADING_ROW + 1)
    {
        totalOccurrencesCell.Value = "0";
    }
    else
    {
        //totalOccurrencesCell.Formula = string.Format("SUM(B5:B{0})", rowToPopulate - 1);
        // TODO: Comment out or remove below after finding out why the above is not working
        totalOccurrencesCell.Formula = "SUM(B5:B19)";
        totalOccurrencesCell.Calculate();
    }
}

What I would think is correct is being applied to that cell, as can be seen here, namely "=SUM(B5:B19)":

So why is "0" the result? The same is true for column C, and D is also catawamptuously chawed up for some reason, too.

This similar code does work elsewhere on the sheet:

using (var totalVarianceCell = priceComplianceWorksheet.Cells[rowToPopulate, DETAIL_TOTALVARIANCE_COL])
{
    totalVarianceCell.Style.Font.Size = DATA_FONT_SIZE;
    totalVarianceCell.Style.Numberformat.Format = NUMBER_FORMAT_CURRENCY;
    totalVarianceCell.Formula = string.Format("SUM(J{0}:J{1})", _firstDetailDataRow, rowToPopulate - 1);
    totalVarianceCell.Calculate();
}

It sums the value in the appropriate range of column J (10), and when clicking in the "sum" cell, it shows "=SUM(J23:J39)" as the value there.

Why would it work in one case, but fail in the others?

NOTE: I am populating the cells like so ("total" is an int):

totalOccurrencesCell.Value = total.ToString("N0", CultureInfo.CurrentCulture);

UPDATE

This is inelegant and a bit disappointing, but as of now, at least, I'm having to "brute force it" this way:

totalOccurrencesCell.Value = SumCellVals(2, 5, rowToPopulate - 1);
. . .
private string SumCellVals(int colNum, int firstRow, int lastRow)
{
    double runningTotal = 0.0;
    double currentVal;
    for (int i = firstRow; i <= lastRow; i++)
    {
        using (var taterTotCell = priceComplianceWorksheet.Cells[i, colNum])
        {
            currentVal = Convert.ToDouble(taterTotCell.Value);
            runningTotal = runningTotal + currentVal;
        }
    }
    return runningTotal.ToString();
}

Answer 1

The clue for the behavior is in this sentence in your question:

NOTE: I am populating the cells like so ("total" is an int):

totalOccurrencesCell.Value = total.ToString("N0", CultureInfo.CurrentCulture);

There is no need to convert numeric values to strings before you assign them to the Value property of a cell.

I can reproduce what you see by running this MCVE:

static void test4()
{
    var fs = new System.IO.FileInfo(@"c:\temp\test4.xlsx");

    if (fs.Exists) fs.Delete();

    using (ExcelPackage package = new ExcelPackage(fs))
    {

        ExcelWorksheet worksheet = package.Workbook.Worksheets["Test"];
        if (worksheet == null) worksheet = package.Workbook.Worksheets.Add("Test");
        var WS = worksheet;
        WS.Workbook.CalcMode = ExcelCalcMode.Manual;

        // strings with .
        WS.Cells[1, 1].Value = "100.0";
        WS.Cells[2, 1].Value = "42.1";
        using (var sum = WS.Cells[3, 1])
        {
            sum.Formula = "SUM(A1:A2)";
            sum.Calculate();
        }

        // strings with ,
        WS.Cells[1, 2].Value = "100,0";
        WS.Cells[2, 2].Value = "42,1";
        using (var sum = WS.Cells[3, 2])
        {
            sum.Formula = "SUM(B1:B2)";
            sum.Calculate();
        }

        // strings with ,
        WS.Cells[1, 3].Value = "1,100";
        WS.Cells[2, 3].Value = "42";
        using (var sum = WS.Cells[3, 3])
        {
            sum.Formula = "SUM(C1:C2)";
            sum.Calculate();
        }

        // let EPPLUS handle it 
        WS.Cells[1, 4].Value = 100; // int
        WS.Cells[2, 4].Value = 42.1d; // double
        using (var sum = WS.Cells[3, 4])
        {
            sum.Formula = "SUM(D1:D2)";
            sum.Calculate();
        }

        package.Save();
    }
}

This is what I get in my Excel sheet as result:

You notice that some cell have the error indicators, but some don't. It is easy to be misled by this. This effect is influenced by the settings for the separators:

In my testing however, column D always was calculated, no matter how I changed the thousands and decimal separator, the SUM was always calculated. Specially if you are converting to strings, using the currentculture isn't a guarantee this will work as the settings in Excel might have been oveeruled.

tl;dr; don't convert numbers (int, doubles, float) to strings but assign them to the value of the cell as is. Both EPPLUS and Excel will handle the correct representation.