# 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

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Brian from Maui

##### MrExcel MVP
Try,

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

#### phxsportz

##### Well-known Member
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``````

#### doobage

##### New Member
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

#### phxsportz

##### Well-known Member
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``````

#### phxsportz

##### Well-known Member
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``````

#### phxsportz

##### Well-known Member
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
3
Views
176
Replies
8
Views
225
Replies
1
Views
122
Replies
2
Views
161
Replies
10
Views
449

### Forum statistics

1,176,126
Messages
5,901,537
Members
434,899
Latest member
powerappsjoker99 ### 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.
Allow Ads at MrExcel

### Which adblocker are you using?    Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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