 Search:

# getting the sum of the last 20 cells

Asked By: Raynard    Date: Mar 13    Category: MS Office    Views: 2441

I am a newbie to Excel VBA. I have a question for the
experts here. I have a table which keeps adding row entries
and it increases vertically. I want to get the sum of the last
20 entries (always the last 20 entries). How do I do that
and which command do I use?

Share:

If the data is in column A, you can try this

lrow = Cells(Rows.Count, 1).End(xlUp).Row
ans = Application.WorksheetFunction.Sum(Cells(lrow - 20 + 1, 1).Resize
(20, 1))
Debug.Print ans

This will work, assuming the data is in Column A. It does the
following:

1. It looks for the first cell in Column A that is NOT empty.
2. It then looks for the next empty cell in Column A
3. It then sums the last 20 cells  of Col A and puts the result in
Cell B1

Sub Last20()

i = 0
FindStart:
i = i + 1
If Cells(i, 1) <> "" Then GoTo FindEnd
GoTo FindStart
FindEnd:
i = i + 1
If Cells(i, 1) = "" Then lrow = i - 1 Else GoTo FindEnd

' Now get sum
rowsum = 0
For i = lrow - 19 To lrow
rowsum = rowsum + Cells(i, 1).Value
Next i
' put sum  in cell B1
Range("B1").Value = rowsum

End Sub

If the data to be summed is not in Column A, then in each instance
of "Cells(i,1)", replace the "1" with the appropriate column index.

pynasocas's suggested code is much more elegant. I don't
understand how to access the Debug.Print statement, but
putting "ans" onto the sheet worked fine.

In the event you have less than 20 rows of cells to sum, you will
get an error unless you substitute my:

"For i = lrow - 19 To lrow"

with:

"For i = Application.WorksheetFunction.Max(1, lrow - 19) To lrow"

A similar change would have to be made in pynasocas's code.

to see the result of Debug.print use the immediate window:
View, Immediate Window or Ctrl + G

A modification to handle when there are <20 rows:

Sub SumLast20RowsInColA()
Dim lrow As Long, ans As Long, lastEntries As Integer
lrow = Cells(Rows.Count, 1).End(xlUp).Row
lastEntries = 20
If lrow < lastEntries Then lastEntries = lrow - 1
ans = Application.WorksheetFunction.Sum(Cells(lrow - lastEntries +
1, 1).Resize(lastEntries, 1))
Debug.Print ans
End Sub

Didn't find what you were looking for? Find more on getting the sum of the last 20 cells Or get search suggestion and latest updates.