Dynamic Subtotal With A Condition

[LancerD]

I created the following formula to count data in visible rows only (when auto filter is enabled). It worked great and the subtotal was dynamic.

=SUBTOTAL(102,A1:A10)

Then, I wanted to add a condition and created the following array formula:

{=COUNT(IF(A1:A10>25%,SUBTOTAL(102,A1:A10)))}

This formula had to be entered using control-shift-enter and it worked – but it was not dynamic and DID NOT adjust for the visible rows only when I filtered using the auto filter function.

Help!

I need a subtotal formula, that allows one condition, and will be dynamic and count only the visible rows when using auto filter. I’ve seen some reference on the Internet to use of the offset function to trick XL into doing this – but have not been able to create a formula to make that work either.

Thanks!

 

[Jonmo1]

Not tested, but I think it would be like this

=SUBTOTAL(102,IF(A1:A10>.25,A1:A10))

entered with ctrl+shift+enter

 

[LancerD]

Thanks. That formula seems like it would work but I get a #value! error. Any other suggestions?

 

[LancerD]

I just figured it out by adapting a "power formula" from one listed on John Walkenbach's website (formula created by Laurent Longre). For others' reference - the formula is:

=SUMPRODUCT(SUBTOTAL(102,OFFSET(A1:A10,ROW(A1:A10)-MIN(ROW(A1:A10)),,1)),--(A1:A10>25%))

Again, this formula enables you to have a conditional (count) subtotal that ignores filtered rows. So it is a dynamic count subtotal based on what you see on your screen.

Don't ask me how it works - but it does work. Very cool! Props to Laurent (and John).

Out.