Dynamic Ranges - summing the last xx variables

doobage

New Member
Joined
Sep 22, 2006
Messages
13
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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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?

Disable AdBlock

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
Back
Top