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
hrh_dash's avatar
hrh_dash
Iron Contributor

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"
HansVogelaar's avatar
HansVogelaar
MVP
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
  • hrh_dash's avatar
    hrh_dash
    Iron Contributor
    Jun 26, 2022
    thanks for the help! code is working perfectly and it is also working perfectly for another line of code. Appreciate the help!

Resources