Dynamic Subtotal With A Condition

LancerD

New Member
Joined
Aug 14, 2007
Messages
3
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!
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Not tested, but I think it would be like this
Code:
=SUBTOTAL(102,IF(A1:A10>.25,A1:A10))
entered with ctrl+shift+enter
 

LancerD

New Member
Joined
Aug 14, 2007
Messages
3
Thanks. That formula seems like it would work but I get a #value! error. Any other suggestions?
 

LancerD

New Member
Joined
Aug 14, 2007
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,416
Messages
5,596,010
Members
414,037
Latest member
Roamingsmile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top