Passing an integer variable into VBA evaluate SumProduct

[Mericle]

I'm trying to pass a variable into my evaluate statement and I can't get the syntax correct. Let me know if you can help

This works:
Labor = Evaluate("=sumproduct(--(g10:g50000="Forecast"), --(j10:J50000="CWIP"), --(y10:y50000=2020), --(z10:z50000=4),(aq10:aq50000))")

but I want to change the 4 to be a variable for the month, so I tried this:
Dim Month as Integer
Month = 4

 

[Fluff]

How about

Labor = Evaluate("=sumproduct(--(g10:g50000=""Forecast""), --(j10:J50000=""CWIP""), --(y10:y50000=2020), --(z10:z50000=" & Month & "),(aq10:aq50000))")

 

[Mericle]

omg it was that easy. I feel so dumb. Thank you so much for your help. Sometimes it just takes another set of eyes!

 

[Fluff]

You're welcome & thanks for the feedback

 

[joeu2004]

This works:
Labor = Evaluate("=sumproduct(--(g10:g50000="Forecast"), --(j10:J50000="CWIP"), --(y10:y50000=2020), --(z10:z50000=4),(aq10:aq50000))")
but I want to change the 4 to be a variable for the month

At a minimum, you might consider:

Labor = Evaluate("SUMIFS(AQ10:AQ50000, G10:G50000, ""Forecast"", J10:J50000, ""CWIP"", Y10:Y50000, 2020, Z10:Z50000," & Month & ")")

SUMIFS should be much more efficient than SUMPRODUCT.

Note the use of two double-quotes where you need one double-qoute in the string. That correction applies in either case.

Both formulas presume that column Z contains month numbers(!), not dates. Is that right?

Finally, you might consider the following alternative:

Labor = WorksheetFunction.SUMIFS(Range("AQ10:AQ50000"), Range("G10:G50000"), "Forecast", Range("J10:J50000"), "CWIP", Range("Y10:Y50000"), 2020, Range("Z10:Z50000"), Month)

Besides the simpler syntax, it might be more efficient to do because all of the calculation is in the VBA thread. In contrast, Evaluate causes the calculation to be done in the Excel thread.

OTOH, the overhead of the Range references might offset any benefit of WorksheetFunction.SUMIFS v. Evaluate. I have not measured it.