[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

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

Your reply is much appreciated!!!
0
mamelas
Asked:
mamelas
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
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
 
barry houdiniCommented:
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
 
barry houdiniCommented:
...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
 
mamelasAuthor Commented:
That's what I was looking for. Thank you very much for your help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now