Dynamic Ranges - summing the last xx variables

doobage

New Member
how would i sum the last xx cells in a dynamic range?

for example, if i wanted to sum the last three cells in the following range, the formula would yield -- 8.

2
3
5
2
1

and for this range, again if i wanted to sum the last three cells, the formula would yield -- 10.

3
4
5
2
1
4
5

thanks in advance for the help

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Try,

=SUM(OFFSET(A2,MATCH(9.99999999999999E+307,A2:A65536)-3,,3))

or if ya wanna do it in VB, one way is as follows..

Code:
`````` Function condsum()
lastrow = Range("A" & Rows.Count).End(xlUp).Row
For x = 0 To 2
t_num = Cells(lastrow - x, 1) + t_num
Next x
condsum = t_num
End Function``````

This works fine if this is the only dynamic range on the worksheet; however, what if I need a formula at the top of multiple dynamic ranges?

For instances, there's a dynamic range in column B starting in row 2 and a dynamic range in column C starting in row 2 and so on and so forth.

Thanks for the quick response. Appreciate any help.

Regards

This isn't the most elegant solution, but it will work for columns A-Z

put this in the Cell you want the result in

=condsum("A") ' Where "A" is the column identifier A B C etc

Code:
`````` Function condsum(col)
lastrow = Range(col & Rows.Count).End(xlUp).Row
celcol = Asc(UCase(col)) - 64
For x = 0 To 2
t_num = Cells(lastrow - x, celcol) + t_num
Next x
condsum = t_num
End Function``````

Still not elegant, but will work with columns beyond Z now

Code:
``````Function condsum(col)
lastrow = Range(col & Rows.Count).End(xlUp).Row
celcol = ((Len(trim(col))-1)*26) + (Asc(ucase(right(col,1)))-64)
For x = 0 To 2
t_num = Cells(lastrow - x, celcol) + t_num
Next x
condsum = t_num
End Function``````

One more change ... to account for the possibility that there isn't 3 rows of data (Assuming the data starts on Row 2, but you can change it at will)

Code:
``````Function condsum(col)
lastrow = Range(col & Rows.Count).End(xlUp).Row
If lastrow < 4 Then
condsum = "NOT ENOUGH DATA"
Exit Function
End If

celcol = ((Len(Trim(col)) - 1) * 26) + (Asc(UCase(Right(col, 1))) - 64)
For x = 0 To 2
t_num = Cells(lastrow - x, celcol) + t_num
Next x
condsum = t_num
End Function``````

Replies
5
Views
155
Replies
6
Views
351
Replies
3
Views
107
Replies
6
Views
193
Replies
3
Views
463

1,219,808
Messages
6,150,351
Members
450,952
Latest member
Zung

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back