VBA for sumifs for visible cells only

[NileshAPatel]

HI
Please help to resolve error in below code

Wants to please sum formula in Cell E163 by selecting E6 to E162, but condition is to sum only those figure if corresponding cell in column D in not empty


Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row

ActiveCell.Formula2R1C1 = _
"=SUMPRODUCT((R6C4:R&lrC4="""")* SUBTOTAL(109, OFFSET(R&[-lr]C, ROW(R&[-lr]C:R[-1]C)- ROW(R&[-lr]C),)))"

Range("E164").Select
End Sub

Thanks !!

 

[Fluff]

How about

ActiveCell.Formula2R1C1 = _
"=SUMPRODUCT((R6C4:R" & lr & "C4="""")* SUBTOTAL(109, OFFSET(R4C, ROW(R6C:R[-1]C)- ROW(R6C),)))"

 

[NileshAPatel]

Thanks a lot , it get resolved, but needs one correction

currently it shows as
=SUMPRODUCT(($D$6:$D$162="")* SUBTOTAL(109, OFFSET(E$6, ROW(E$6:E162)- ROW(E$6),))) in place of E$6 it will show as E6 as shown below

=SUMPRODUCT(($D$6:$D$162="")* SUBTOTAL(109, OFFSET(E6, ROW(E6:E162)- ROW(E6),)))

Thanks

 

[Fluff]

Why do you need that as a relative reference?

 

[NileshAPatel]

Thanks a lot, my problem get solved