Subtotal variable range

J

JDaywalt

Is there a way to adjust the range in a subtotal formula to automatically
recognize the last row of data to allow for a high degree of variability in
the number of rows it contains? I realize that I could set the subtotal
range to be, for example, B3:B65000 -- however, users may delete rows over
time and ultimately this range may "shrink" to the point where it is not
capturing all of the data. Was hoping there was some sort of "last row"
reference that could be used to ensure integrity of the formula over time.
 
V

vezerid

You have several ways, here is one, which is an *array* formula
(commit with Shift+Ctrl+Enter)

=SUBTOTAL(3,INDIRECT("B3:B"&MAX(IF(B3:B65536<>"",ROW(B3:B65536)))))

HTH
Kostis Vezerides
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro Pause to Select range then Subtotal 0
Can I use the subtotal function while a worksheet is protected? 0
Trying to find additional ranges outside of min, max, and average 2
nested subtotal with sumproduct 0
subtotal filter with growing range 2
Subtotals from Named Range on other worksheet 5
Analyzing survey results 6
Counting multiple occcurrences between date range 3

Top