# Dynamic Subtotal With A Condition

#### LancerD

##### New Member
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!

### Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Not tested, but I think it would be like this
Code:
``=SUBTOTAL(102,IF(A1:A10>.25,A1:A10))``
entered with ctrl+shift+enter

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

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.

Replies
5
Views
126
Replies
1
Views
149
Replies
3
Views
342
Replies
11
Views
500
Replies
3
Views
153

1,220,979
Messages
6,157,184
Members
451,403
Latest member
scorpidxls

### 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?

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