Forum Discussion

hrh_dash's avatar
hrh_dash
Iron Contributor
Jun 24, 2022

How should i write the sumifs .formula vba so that the code runs smoothly

How should i write the SUMIFS macro code by using the .Formula method so that the SUMIFS will execute correctly?    With ws.Range("M2") .Formula = "=SUMIFS('C:\Users\hrhquek\Desktop\[calculate...
excel
Formulas and Functions
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jun 26, 2022

    hrh_dash 

    How about

        With ws.Range("M2")
        .NumberFormat = "#,##0.00"
        .Formula = "=SUMIFS($I:$I,$F:$F,"">60"",$G:$G, ""USD"")"
        .Value = .Value
    End With

    or

        With ws.Range("M2")
        .NumberFormat = "#,##0.00"
        .Value = Evaluate("=SUMIFS($I:$I,$F:$F,"">60"",$G:$G, ""USD"")")
    End With

    or

        With ws.Range("M2")
        .NumberFormat = "#,##0.00"
        .Value = Application.SumIfs(Range("I:I"), Range("F:F"), ">60", Range("G:G"), "USD")
    End With
HansVogelaar's avatar
HansVogelaar
MVP
Jun 24, 2022

hrh_dash 

For example:

With ws.Range("M2")
    .Formula = "=SUMIFS('C:\Users\hrhquek\Desktop\[calculate aging days macro.XLSM]Sheet2'!I2:I1000," & _
        "'C:\Users\hrhquek\Desktop\[calculate aging days macro.XLSM]Sheet2'!F2:F1000,"">60""," & _
        "'C:\Users\hrhquek\Desktop\[calculate aging days macro.XLSM]Sheet2'!G2:G1000,""USD"")"
    .Value = .Value
End With
  • hrh_dash's avatar
    hrh_dash
    Iron Contributor
    Jun 25, 2022

    HansVogelaar , thanks and appreciate for helping out again. The code is populating an error message:

    Run-time error '1004':

    Application-defined or object-defined error

     

    Excel formula would be: SUMIFS($I:$I,$F:$F,"">60"",$G:$G, ""USD"")

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jun 25, 2022

      hrh_dash 

      The code should work, unless you misspelled the file path or sheet name. I tested it.

      • hrh_dash's avatar
        hrh_dash
        Iron Contributor
        Jun 26, 2022

        HansVogelaar , i tried to remove the filepath as the code is running the workbook that is containing the code., there is no error fortunately but there is no data populating in the sheet. I tried replacing it with a range; ie I2:I10000; no data is populating as well.

         

        With ws.Range("M2")
   .Formula = "=SUMIFS('I:I," & "'F:F,"">60""," & "'G:G,""USD"")"
    .Value = .Value
End With

         

        Currently i am using this code but it is showing the formulas which i do not want to.

        ws.Range("M2").Formula = "=SUMIFS($I:$I,$F:$F,"">60"",$G:$G, ""USD"")"
ws.Range("M2").NumberFormat = "#,##0.00"

Resources