Need to sum the bottom 5 cells that have values....

[jasgot]

A	B
100	1
100	2
500	3
400	4
100	5
200	6
	7
200	8
100	9
100	10

I want to sum a maximum of the bottom five rows that have values. I want to skip the blank rows.
So the results of the formula in B10 should be 100+100+200+200+100 = 700

I will use this formula in an adjacent column to get a rolling sum of a maximum of the last five rows
Where there are not five rows, I just want the rows that have values
So the result in B1 will be 100 (A1:A1) - because there is only one row
the result in B2 will be 200 (A1:A2) - because there are only two rows
the result in B3 will be 700 (A1:A3)- because there are only three rows
the result in B4 will be 1100 (A1:A4) - because there are only four rows
the result in B5 will be 1200 (A1:A5) - because there are only five rows
the result in B6 will be 1300 (A2:A6) - because there are more than five rows
the result in B7 will be 1300 (A2:A6) - because we had to go up one more to get 5 values because A7 is empty
the result in B8 will be 1400 (A3:A8) - because we had to go up one more to get 5 values because A7 is empty
the result in B9 will be 1000 (A4:A9) - because we had to go up one more to get 5 values because A7 is empty
the result in B10 will be 700 (A5:A10) - because we had to go up one more to get 5 values because A7 is empty

I need the formula to determine which rows should be included (they must have a value) because I cannot hard code the range like I did here.

The goal is to sum the most recent five values, there may actually be several blanks that have to be skipped (sometimes).

Thanks.

Jason

 

[Anonymous1378]

I stand corrected; I assumed it was an array formula issue; I don't have time to look at it right now, so I'll check in again later

 

[jasgot]

I stand corrected; I assumed it was an array formula issue; I don't have time to look at it right now, so I'll check in again later

I really appreciate you taking the time to help. This is really driving me nuts. It should work, it's not an obscure set of functions.

 

[Peter_SSs]

Try this

23 08 03.xlsm
	A	B
1	100	100
2	100	200
3	500	700
4	400	1100
5	100	1200
6	200	1300
7
8	200	1400
9	100	1000
10	100	700
Sum last 5

Cell Formulas
Range	Formula
B1:B10	B1	=IF(A1="","",SUM(INDEX(A$1:A1,LARGE(IF(A$1:A1<>"",ROW(A$1:A1)-ROW(A$1)+1),MIN(5,COUNT(A$1:A1)))):A1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jasgot]

That works a treat! Thank you so much!!!!
Can I buy you a drink?

Try this

23 08 03.xlsm
	A	B
1	100	100
2	100	200
3	500	700
4	400	1100
5	100	1200
6	200	1300
7
8	200	1400
9	100	1000
10	100	700
Sum last 5

Cell Formulas
Range	Formula
B1:B10	B1	=IF(A1="","",SUM(INDEX(A$1:A1,LARGE(IF(A$1:A1<>"",ROW(A$1:A1)-ROW(A$1)+1),MIN(5,COUNT(A$1:A1)))):A1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

That works a treat! Thank you so much!!!!

You're welcome. Thanks for the follow-up.

Can I buy you a drink?

Sure - when we meet in person.