3

I'm trying to add a formula to a cell in multiple rows in Excel through C#. What i'm trying to achieve with the formula is to SUM all the cells which does not contain the string N/A, in column D to S in every row.

This is the formula i'm trying to add: 

=SUMIF(DX:SX,"<>N/A")

(In the example above i changed the actual row number with X)

So i'm looping through the rows and doing this:

    foreach (var row in rows)
    {
        ws.Cells[row, 2].Formula = string.Format("=SUMIF(D{0}:S{0},\"<>N/A\")", row + 1);                          
    }

Here I get the exception:

System.ArgumentException : Failed to parse: =SUMIF(D2:S2,"<>N/A"). Error: Unsupported function: SUMIF.For list of supported functions consult GemBox.Spreadsheet documentation.

I've also tried to add the Swedish version of the formula which is: 

=SUMMA.OM(DX:SX;"<>N/A")

(In the example above i changed the actual row number with X)

and here i get the exception:

System.ArgumentException : Failed to parse: =SUMMA.OM(D2:S2;"<>N/A"). Error: Not expected: SUMMA

The weirdest part of this is that if i manually enter the swedish formula in the cell in Excel, it works.

Appreciate any help i can get, thank you so much.

N8888
  • 670
  • 2
  • 14
  • 20
Brewsli
  • 127
  • 1
  • 10

1 Answers1

0

I actually don't see any reason it would throw that error -- what you did seems like it should work. In the spirit of offering another avenue to test, you could also try the R1C1 version of the formula and see if that works:

foreach (var row in rows)
{
    ws.Cells[row, 2].FormulaR1C1 = "=SUMIF(R[0]C[2]:R[0]C[17],\"<>N/A\")";
}

Unless your rows variable contains an invalid number, I'm not sure why it would throw that exception. In the example error you gave, that should be fine.

Hambone
  • 15,600
  • 8
  • 46
  • 69
  • Hey, i completely agree. I think it's very strange that this doesn't work. What is the FormulaR1C1 though? It deosn't pop up for me when i write .Formula, do i need to add any references? – Brewsli Jul 27 '18 at 07:00
  • No, this should be standard. My guess is because of interop, intellisense doesn't give you a lot of help. Try defining a generic range (ie `Range r;`) and then doing `r.`, and I believe you will see Formula and FormulaR1C1 both pop up as options. R1C1 is relative row and column (Row X, Column X), meaning the R1C1 version of the formula is the same when you copy and paste it. I still see nothing wrong with your code, which leads me to believe it, at some point, hits an illegal range, such as entering a formula "=A-1" – Hambone Jul 27 '18 at 19:00