0

Could you please help me on the following issue. Thank you in advance.

I need to insert a SUMIF formula in a range using vba. I could insert a SUM formula in the range. Please see below;

ActiveSheet.Range("G" & LastrowAS).Formula = "=SUM(G3 :GG)"

But when I try to have SUMIF, I face an error.

ActiveSheet.Range("G" & LastrowAS).Formula = "=SUMIFS(G6:GG;G6:GG;"<>#VALUE!"; G6:GG; "<>#N/A")"

enter image description here

AM-NL
  • 7
  • 2
  • You can see error from the attached image. – AM-NL Jun 25 '20 at 12:53
  • There's no way that first line works either. `G3:GG` is not a valid range reference. – BigBen Jun 25 '20 at 12:53
  • I have already defined GG Set rng = ActiveSheet.Range("G" & 3 & ":G" & LastrowAS - 1) ActiveWorkbook.Names.Add Name:="GG", RefersTo:=rng – AM-NL Jun 25 '20 at 12:54
  • Then your issue is the double quotes (they need doubled up), and you have to use `,` instead of `;`. See the linked duplicates. `"=SUMIFS(G6:GG,G6:GG,""<>#VALUE!"",G6:GG,""<>#N/A"")"` – BigBen Jun 25 '20 at 12:57
  • I had to do that because this formula will be used in a new exported excel file . and every time number of rows is different – AM-NL Jun 25 '20 at 12:57
  • 1
    Thank you so much. – AM-NL Jun 25 '20 at 13:59

0 Answers0