vba to find the sum product of filtered data

[abhi_jain80]

Hi,

I have filtered the data using the below vba code which is filtering the rows if column G is not blank and column F is 0 and column F is blank.

Range("L6") = Application.CountIfs(Columns("G:G"), "<>", Columns("F:F"), "=0") + Application.CountIfs(Columns("G:G"), "<>", Columns("F:F"), "")

The count of rows filtered out is 3 which is fine. Now I need to find the sum product of column G and column H of the filtered data but struggling to get the code, can somebody help me please? Image attached...

Apologies if this has been solved earlier and thanks in advance...

Abhi

 

[abhi_jain80]

Let's assume that Sheet1 contains the source data, try...

    Dim lastRow As Long
    With Worksheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
    End With
   
    With Sheets(2).Range("A10")
        .Formula = "=SUMPRODUCT(--('Sheet1'!F2:F" & lastRow & "=0),--('Sheet1'!G2:G" & lastRow & "<>""""),'Sheet1'!G2:G" & lastRow & ",'Sheet1'!H2:H" & lastRow & ")"
        .Value = .Value
    End With

By the way, just in case you are not aware of it, Sheets(2) refers to your sheet by index number, not by name. If you actually want to refer to your worksheet called "Sheet2", then you would need to refer to it as Worksheets("Sheet2").

Hope this helps!

Great, this helps a lot. Thank you

 

[Domenic]

You're very welcome, cheers!