# Sum variable range in excel

Dear Excel Experts,

Suppose column A contains numbers and zeroes in random order.
I want to create a formula in Column B where in case the value of the Column A for the specific row is non zero, it will produce the sum of that cell in Column A plus the three non zero cells of column A above that row.

For example

Column A contains the values, the non zero being e.g. A6, A9, A10, A14

Column B will sum for example in cell B14 (which is adjacent to the non-zero value at A column), A14 plus the three non zero cells above A14, ie A6+A9+A10

###### Who is Participating?

Commented:
...or alittle shorter.....

=IF(A6=0,"",IFERROR(SUM(IF(ROW(A\$6:A6)>=LARGE(IF(A\$6:A6<>0,ROW(A\$6:A6)),4),A\$6:A6)),""))

.....still confirmed with CTRL+SHIFT+ENTER

If data starts at a differnt row just change all A6 refs as appropriate

regards, barry
0

EngineerCommented:
Try this formula for row 6

=IF(A6>0,A6+INDEX(A:A,LARGE(IF(\$A\$1:A5>0,ROW(\$A\$1:A5)),1))+INDEX(A:A,LARGE(IF(\$A\$1:A5>0,ROW(\$A\$1:A5)),2))+INDEX(A:A,LARGE(IF(\$A\$1:A5>0,ROW(\$A\$1:A5)),3)),"")
0

Commented:
Assuming that you have Excel 2007 or later you can use this formula in B6

=IF(A6=0,"",IFERROR(SUM(INDEX(A\$6:A6,LARGE(IF(A\$6:A6<>0,ROW(A\$6:A6)-ROW(A\$6)+1),4)):A6),""))

confirm with CTRL+SHIFT+ENTER and copy down column

If there aren't 3 non-zero numbers above you just get blanks, see attached example where A1 has random zeroes/non-zeroes. Press F9 to re-generate random numbers

regards, barry
sumlast4.xlsx
0

Author Commented:
That's what I was looking for. Thank you very much for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.