Subtotal variable range
 

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.

Subtotal variable range
 

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<"",R OW(B3:B65536)))))

HTH
Kostis Vezerides

On May 20, 8:58 pm, JDaywalt
wrote:
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.