Only the last rows...

[Guest]

I have a spreadsheet with about 50 rows that gradually will be filled up with
numbers. How can I make formula that will count and ad up only the 12 last
rows at any given time? The position of the last 12 rows will here obviously
change over time, since more numbers will be added frequently.

Thanks very much for any help.

 

[Trevor Shuttleworth]

Lars

one way, assuming no header row:

=IF(ISERROR(OFFSET(A1,COUNTA(A:A)-12,)),"not enough
rows",SUM(OFFSET(A1,COUNTA(A:A)-12,):OFFSET(A1,COUNTA(A:A),)))

This also assumes the data is in column A

Regards

Trevor

 

[L. Howard Kittle]

Trevor is 'da man!!

I tried formula and I ain't even close. Did a code but I think it lacks a
bunch if you have 50 columns, how clunky if you have to adapt this to 50
columns. Simple on one column... Some MVP's can loop this through 50
columns nicely but I can't.

On the worksheet you enter =Sum(Data)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub

Dim i As Integer
i = Range("A1").End(xlDown).Row - 1

If Target.Row < 12 Then
Range("A1").End(xlDown). _
Offset(-i, 0). _
Resize(i + 1, 1). _
Name = "Data"
ElseIf Target.Row > 12 Then
Range("A100").End(xlUp). _
Offset(-11, 0). _
Resize(12, 1). _
Name = "Data"
End If
End Sub

HTH
Regards,
Howard