Sumif on special cells

[SQUIDD]

Hi All

Hope we are all good.

Im trying to work out if i can sumif on visible cells after a filter.

The info is in a table, if that matters.

The below works really well on counta

Set RNG1 = Range("B11:B" & Range("B65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
R1 = Application.WorksheetFunction.CountA(RNG1)

So Based on that i assumed the below would also work, but no. Have a made a silly mistake, or is it not possible. Should i take a different approach?

Set RNG2 = Range("H11:H" & Range("H65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
R2 = Application.WorksheetFunction.CountIf(RNG2, "1ST")

Thanks for help in advance.

Dave

 

[Peter_SSs]

Try this instead

Set RNG2 = Range("H11", Range("H" & Rows.Count).End(xlUp))
R2 = Evaluate(Replace("SUMPRODUCT(SUBTOTAL(3,OFFSET(#,ROW(#)-ROW(" & RNG2.Cells(1).Address & "),0,1))*(#=""1ST""))", "#", RNG2.Address))

 

[SQUIDD]

Hi Peter.

That workes absolutly spot on, thanks very much for your help.

Now im not sure if you could help me again, since the approach has changed completly, im not sure how to do the last bit.

Maybe i should start a new post, but it seems close to the 1st question.

I will ask either way, and if a new post, let me know. thankyou very much again.

LR = Range("A" & Rows.Count).End(xlUp).Row
Application.WorksheetFunction.SumIf(Range("H11:H" & LR), "1ST", Range("I11:I" & LR))

Where i want to just sum the visible clls again.

Dave

 

[Peter_SSs]

Assuming dSUM is the variable to hold the sum, try these two changes

Set RNG2 = Range("H11", Range("H" & Rows.Count).End(xlUp))
dSUM = Evaluate(Replace("SUMPRODUCT(SUBTOTAL(9,OFFSET(#,ROW(#)-ROW(" & RNG2.Cells(1).Address & "),1,1))*(#=""1ST""))", "#", RNG2.Address))

The 9 tells SUBTOTAL to SUM rather than COUNTA
The 1 indicates that the column to sum is offset 1 column to the right of the "1ST" column. That is, sum column I because the "1ST" column is H.

 

[SQUIDD]

Peter

Thanks for the explintion on that. I really appriciate that.

And all good, thankyou for your help on this.

Dace

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.