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]

As I have no clue what excel version you're using, here's a solution in Office 365.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jasgot]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks! I just did. This is for Office Standard 2016

 

[Fluff]

Thanks for that, but do you really use all those different versions?
How about

Fluff.xlsm
	A	B	C
1	100	1	100
2	100	2	200
3	500	3	700
4	400	4	1100
5	100	5	1200
6	200	6	1300
7		7	1300
8	200	8	1400
9	100	9	1000
10	100	10	700
11
Sheet4

Cell Formulas
Range	Formula
C1:C10	C1	=SUM(INDEX(A:A,AGGREGATE(14,6,ROW(A$1:A1)/(A$1:A1<>""),ROW(INDIRECT("1:"&MIN(COUNTIFS(A$1:A1,"<>"),5))))))

 

[jasgot]

Thanks. I should have removed column B after I created the posts. Column B was for me to keep track of the rows while I was adding them.

I can't get your formulas to work unless I copy the whole sheet in your example. But then if I remove column B, it just shows me the value in column A, not the SUM of the 5 rows.

 

[jasgot]

Thanks for that, but do you really use all those different versions?

Not on the same PC....

 

[jasgot]

 

[Anonymous1378]

The formula from Fluff looks right to me; pretty sure you just need to enter it as an array formula using Ctrl-Shift-Enter instead of Enter.

 

[jasgot]

That didn't help either.