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)
Code:
=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)
Code:
=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)
Code:
=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)
Set the following formula in cell F2 (copy down to F1000)
Set the following formula in cell G2 (copy down to G1000)
Code:
=IFERROR(LOOKUP(2,1/($A$2:$A$1000=F2),$B$2:$B$1000),"")
Set the following formula in cell I2 (copy down to I1000)
Set the following formula in cell J2 (copy down to J1000)
Code:
=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)
Set the following formula in cell F2 (copy to last the desired row)
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.