How do I continue to take the sum of the last 50 non-zero numbers?

[radiotower]

Each day, I put a large number in one column and 0 in the other. I need to be able to take the sum of the last 50 non-zero numbers for each column. The cell after that should take the sum of the 2nd-51st numbers (excluding 0s), then the next cell should take the sum of the 3rd-52nd numbers (excluding 0s), etc.

My issue is, when I move to the 51st non-zero number in a column, I do not know how to exclude the 1st non-zero number in the same column.

https://s30.postimg.org/pub2pak2p/Sample_Pic.jpg

 

[DRSteele]

I think this is what you're after. It sums the yellow numbers, which are the prior five non-zero numbers in a list that grows downwards. Adapt it for your needs.

reference: http://www.mrexcel.com/forum/excel-questions/739875-sum-last-5-non-zeros-row.html

	A	B
1	0
2	10
3	12
4	0
5	15
6	17
7	23
8	5
9	0
10	0
11	2
12	18
13	0
14	0
15	26	74

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Array Formulas Cell Formula B15 {=SUM(IF(ROW(A1:A15)>=LARGE(IF(ISNUMBER(1/A1:A15),ROW(A1:A15)), MIN(COUNT(1/A1:A15),5)),A1:A15))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself.

<tbody>
</tbody>

 

[Macropod]

Cross-posted at:
How do I continue to take the sum of the last 50 non-zero numbers?
and:
How do I continue to take the sum of the last 50 non-zero numbers?

Please read our policy on Cross-Posting in rule #13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[radiotower]

Cross posts: How do I continue to take the sum of the last 50 non-zero numbers?
How do I continue to take the sum of the last 50 non-zero numbers?

 

[navic]

The cell after that should take the sum of the 2nd-51st numbers (excluding 0s), then the next cell should take the sum of the 3rd-52nd numbers (excluding 0s), etc.

Perhaps I did not fully understand you well, but here's my example solutions.
You can create a new helper Sheet2 (see figure below)

On helper Sheet2

Set the following formula in cell A2 (copy down to A1000)

=IF(B2<>"",COUNTIF($B$2:$B$1000,"<>""")-COUNTBLANK($B$2:$B$1000))-ROW(A1)+1

Set the following ARRAY formula in cell B2 (copy down to B1000)

=IFERROR(INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!A$1:A$1000<>0,ROW(A$1:A$1000)),ROWS(B$1:B2))),"")

Set the following ARRAY formula in cell C2 (copy down to C1000)

=IFERROR(INDEX(Sheet1!B$1:B$1000,SMALL(IF(Sheet1!B$1:B$1000<>0,ROW(B$1:B$1000)),ROWS(C$1:C2))),"")

Set the following formula in cell D2 (copy down to D1000)

=ROW(A1)

Set the following formula in cell F2 (copy down to F1000)

=ROW(A1)

Set the following formula in cell G2 (copy down to G1000)

=IFERROR(LOOKUP(2,1/($A$2:$A$1000=F2),$B$2:$B$1000),"")

Set the following formula in cell I2 (copy down to I1000)

=ROW(A1)

Set the following formula in cell J2 (copy down to J1000)

=IFERROR(LOOKUP(2,1/($D$2:$D$1000=I2),$C$2:$C$1000),"")

Note:
Instead formula =ROW(A1), you can set ordinal numbers 1,2,3,4 ... etc.
Column B and C are values copied from Sheet1 without Zero value
-------------------------------------------

On Sheet1

Set the following formula in cell E2 (copy to last the desired row)

=SUM(Sheet2!G2:G51)

Set the following formula in cell F2 (copy to last the desired row)

=SUM(Sheet2!J2:J51)

Note: Column D is visually informative range rows.
btw: Of course, if this can help to you, you need adapt formula to your real problem.