SUMPRODUCT Formula with Dynamic Last Row

Hello Everyone,

I've looked online and found a couple threads that were similar but nothing that was exactly what I was looking for. I am trying to put together a SUMPRODUCT formula with a dynamic last row. I think my issue isn't with the conditions, but with the column that it's supposed to be summing. Here is the code.

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
dLastRow = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
    Range("R2").Formula = "=SUMPRODUCT(--('Data'!C$2:C$" & dLastRow & "=C2),--('Data'!I$2:I$" & dLastRow & "=H2),('Data'!Z$2:Z$ & dLastRow))"
    Range("R2").AutoFill Destination:=Range("R2:R" & LastRow), Type:=xlFillDefault

If someone could show me what I have wrong here, I'd appreciate it.

Thanks!

Yeah, I believe that was one of the little changes I tried out. I end up getting a "Compile error: Expected: end of statement" error that highlights the second to last parenthesis.

Ahhh - I think I got it... I just added one more quotation after 'Data'!Z$2:Z$. So the final code looks like:

Range("R2").Formula = "=SUMPRODUCT(--('Data'!C$2:C$" & dLastRow & "=C2),--('Data'!I$2:I$" & dLastRow & "=H2),('Data'!Z$2:Z$" & dLastRow & "))"