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

##### New Member
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

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### DRSteele

##### Well-known Member
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

AB
10
210
312
40
515
617
723
85
90
100
112
1218
130
140
152674

</tbody>
Sheet6

Array Formulas
CellFormula
B15{=SUM(IF(ROW(A1:A15)>=LARGE(IF(ISNUMBER(1/A1:A15),ROW(A1:A15)), MIN(COUNT(1/A1:A15),5)),A1:A15))}

</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>

• deciog

#### navic

##### Active Member
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)
Code:
``=ROW(A1)``

Set the following formula in cell F2 (copy down to F1000)
Code:
``=ROW(A1)``

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)
Code:
``=ROW(A1)``

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)
Code:
``=SUM(Sheet2!G2:G51)``

Set the following formula in cell F2 (copy to last the desired row)
Code:
``=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.

Replies
9
Views
164
Replies
12
Views
254
Replies
2
Views
112
Replies
10
Views
1K
Replies
2
Views
137

### Forum statistics

1,191,485
Messages
5,986,860
Members
440,055
Latest member
CraigTriesHisBest ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back