SOLVED

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

Iron Contributor

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!

6 Replies

@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

@Hans Vogelaar , 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"")

@hrh_dash 

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

@Hans Vogelaar , 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"
best response confirmed by hrh_dash (Iron Contributor)
Solution

@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
thanks for the help! code is working perfectly and it is also working perfectly for another line of code. Appreciate the help!
1 best response

Accepted Solutions
best response confirmed by hrh_dash (Iron Contributor)
Solution

@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

View solution in original post