Variable Sum Up Problem

[manthony]

Hi,

This is an odd formula I am looking for. I have a formula to get me to the last row in column D. I would then like to sum from the last row in col. D up X number of rows. The X is determined by a count formula on a different sheet titled duplicates.

My problem is the sum range is variable and will always be in column D but the summing row starting position (last row in column D) and number of rows to be summed (# counted on the duplicates sheet) are variable. Below is an example of what I am looking for.

Duplicates count = 5

Col./Rows	A	B	C	D	E	F
1
2				3
3				5
4				7
5				11
6				8
7					= leave one row blank
8				34	=sum formula
9

<tbody>
</tbody>

Many thanks for the help.

 

[Legacy 143009]

How about to try INDIRECT function?

=SUM(INDIRECT("D"&(your last row formula)-(your count formula)&":D"&(your last row formula)))

 

[manthony]

Hi thanks for the help. Sorry, I was not clear. My last row formula is vba code that selects to last row in column D. My count formula is COUNTA(Duplicates!B:B)-1). Any ideas on how to combined it with your formula?

 

[Legacy 143009]

If you are using vba, do the all job there. You can try something like:

Dim count As Integer
count = [COLOR=#000080][FONT=&quot]Evaluate("[/FONT][/COLOR][COLOR=#333333]COUNTA(Duplicates!B:B)-1)[/COLOR][COLOR=#000080][FONT=&quot]")[/FONT][/COLOR]
Sheets("Sheet1").[C1].Formula ="=SUM(D"&lastRow-count&":D"&lastRow&")"

 

[Aladin Akyurek]

Where are you wanting to enter the sum formula? In column D or outside that column?

 

[manthony]

Thanks, but I am getting an expected end of statement error on the last line on the last ":D" section.

 

[manthony]

I would like to enter the formula in column D after the last row with a value in it.

 

[Aladin Akyurek]

I would like to enter the formula in column D after the last row with a value in it.

If you want it enter after the blank row following the last data row...

=SUM(OFFSET(INDEX(D:D,ROW()-2),0,0,-5))

where you can replace 5 with the cell housing your count.

Outside D, say in E1, you could invoke the following formula:

=SUM(OFFSET(INDEX(D:D,MATCH(9.99999999999999E+307,D:D)),0,0,-5))

Again, here also you can replace 5 with the cell housing that number.

 

[manthony]

Hi Aladin, You are a lifesaver. This formula works perfectly. Thank you so much.

 

[Aladin Akyurek]

Hi Aladin, You are a lifesaver. This formula works perfectly. Thank you so much.

Glad to help. Thanks for the kind update.