EPPlus SUMIFS return value #VALUE

Question

I am generating excel file with EPPlus. at the end i am try to get sum of values of a column but getting #VALUE!

i used SUM formula first but got the same error. Here is a sample code.

for (int x = 2; x <= totalIteration; x++)
{
    startperiodletter = ColumnIndexToColumnLetter(x) + 2;
    endperiodletter = ColumnIndexToColumnLetter(x) + totalrows;
    tmpCell = ColumnIndexToColumnLetter(x) + (totalrows+1);

    ws.Cells[tmpCell].Formula = "=SUMIFS(B2:B99,B2:B99,>0)";
    ws.Cells[tmpCell].Calculate();
    val = (ws.Cells[tmpCell].Value == null ? "" : ws.Cells[tmpCell].Value.ToString());
}

ws.Cells[tmpCell].Formula = "=SUMIFS(B2:B99,B2:B99,>""""0"""")"; This way i use SUMIF formula with EPPLus and when i try to read value then getting value like #VALUE! i am reading value this way val = (ws.Cells[tmpCell].Value == null ? "" : ws.Cells[tmpCell].Value.ToString());

this line is not working as expected. my few cells are empty and i want to get sum of those cell value which is not empty.

please rectify me where i made the mistake in above code.

Answer 1

I did a simple test. In the rules of this package, the formula does not need to add the preceding "=".

After deleting it and adding double quotes to >0 as suggested by Viorel-1, it works fine.

currentWorksheet.Cells["C4"].Formula = "SUMIFS(B2:B99,B2:B99,\">0\")";

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.