Subtotal variable range

  • Thread starter Thread starter JDaywalt
  • Start date Start date
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.
 
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
 
Back
Top