SUMIF Excluding hidden rows

Hello

I am having some difficulty working out how to exclude hidden (filtered) rows when using a SUMIF function.

=SUMIF(X8:X3000),2.0647,T8:T3000)

Can someone please assist with this problem

MT

Hello,

If your data is filtered by column X and the filter criteria is 2.0647 then you can just sum all the visible rows using the Subtotal function:

=SUBTOTAL(9,T8:T3000)



If you want to conditionally sum visible cells that could be filtered in any way, try this formula instead:

=-SUMPRODUCT(SUBTOTAL(3,OFFSET(T8,ROW(T8:T3000)-ROW(T8),,1)),-(X8:X3000=2.0647),(T8:T3000))

Colin

Originally Posted by colin_l

=-SUMPRODUCT(SUBTOTAL(3,OFFSET(T8,ROW(T8:T3000)-ROW(T8),,1)),-(X8:X3000=2.0647),(T8:T3000))

...or you can shorten a little by using 9 rather than 3 as the first argument for SUBTOTAL, i.e.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(T8,ROW(T8:T3000)-ROW(T8),0)),--(X8:X3000=2.0647))

Most definitely! Nice improvement, daddylonglegs.

Silly of me since I even used 9 in the simpler version!

Colin

Ahhh thankyou very much

That is exactly what I am after