How to use a subtotal with a filtered range that is dynamic???

**jfoerch** · 11-02-2015, 06:12 PM

I tried, but couldn't get it to work... I've got a spreadsheet where I'm using the Subtotal function on a range that is filtered. That all works fine. However, I have some VBA code that will increase and decrease the range size for that subtotal. Is there a way to write the formula so that the subtotal function works as I filter the data, but it also re-sizes itself as the overall range grows and shrinks? Here are the options that I tried...

=SUBTOTAL(9,AN4:COUNTA(Targeted Business List!$AN$4:$AN$1000),1)

=SUBTOTAL(9,OFFSET(Targeted Business List!$AN$4,0,0,COUNTA(Targeted Business List!$AN$4:$AN$15000),1))
* This version came from code that I'm using for a named range that dynamically changes the size of the named range.

Neither of those worked, but I figure there has to be a way. Any help would be appreciated.

Thanks!!!

**FlameRetired** · 11-02-2015, 08:13 PM

Without an uploaded example Excel file representative of what you are trying to do it is difficult for me to say.

A shot in the dark, though. If you can live with a helper column I have found that this approach works well. Next to your filtered range try this formula in the first row and fill down.

Formula:

Please Login or Register  to view this content.

Change the left and right boundaries of your filtered range to suit. Then reference that helper column in formula with value of helper = 1, or reference the entire helper range in an array formula.

Is there a way to write the formula so that the subtotal function works as I filter the data, but it also re-sizes itself as the overall range grows and shrinks?

A Dynamic Named Range (DNR) formula in Name Manager comes to mind. It's topic in itself. There is no shortage of information with query in this forum and many different ways to do it.

Also Tables will grow to accommodate new data, but I don't know how well they get along with this helper column idea or with macros.

Also in SUBTOTAL you need to use the 100 series of function numbers for SUBTOTAL to ignore hidden rows. Try SUBTOTAL(109, <your formula>)

**jfoerch** · 11-03-2015, 09:40 AM

Figured it out. Had this formula in a separate portion of the spreadsheet, only it was using "sum" instead of subtotal. So, I added the stuff after the "9," and changed to suite and it worked. Not exactly sure why it works, but it does.

=SUBTOTAL(9,AN4:INDEX(AN:AN,MATCH(9.99999E+307,AN:AN)))

As always, thanks for the help!

**FlameRetired** · 11-03-2015, 05:56 PM

You're welcome. Glad you found the solution. Thanks for the rep and the feedback.

How to use a subtotal with a filtered range that is dynamic???

LinkBack

Thread Tools

Rate This Thread

Display

How to use a subtotal with a filtered range that is dynamic???

Re: How to use a subtotal with a filtered range that is dynamic???

Re: How to use a subtotal with a filtered range that is dynamic???

Re: How to use a subtotal with a filtered range that is dynamic???

Thread Information

Users Browsing this Thread

Similar Threads

Copy dynamic specified, filtered cells/range from one sheet to multiple sheets

Dynamic Range Add Rows and Sum/Subtotal row Formula or VBA?

CountIf and SubTotal Help for filtered results

[SOLVED] Subtotal a filtered range grrrr

Subtotal a filtered range

[SOLVED] Dynamic Named Range: Simulating a filtered region

Bookmarks

Bookmarks

Posting Permissions