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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

phxsportz

Well-known Member
Joined
Jun 11, 2006
Messages
1,985
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
Joined
Sep 22, 2006
Messages
13
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
Joined
Jun 11, 2006
Messages
1,985

ADVERTISEMENT

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
Joined
Jun 11, 2006
Messages
1,985
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
Joined
Jun 11, 2006
Messages
1,985
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
 

Forum statistics

Threads
1,141,757
Messages
5,708,346
Members
421,566
Latest member
7Nabisco

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
Top