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 aging days macro.XLSM]Sheet2'!$I2:$I," _
    ,'C:\Users\hrhquek\Desktop\[calculate aging days macro.XLSM]Sheet2'!$F2:$F,"">60"" _
    ,'C:\Users\hrhquek\Desktop\[calculate aging days macro.XLSM]Sheet2'!$G2:$G, ""USD"")"
    .Value = .Value
    
End With

thanks in advance and appreciate the help!

Excel

Formulas and Functions

How about

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

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

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

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
  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
    MVP
    Jun 25, 2022
    hrh_dash
    
    The code should work, unless you misspelled the file path or sheet name. I tested it.

Forum Discussion

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

Share

Resources