?
Solved

Sum variable range in excel

Posted on 2013-01-21
4
Medium Priority
?
496 Views
Last Modified: 2013-01-22
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
Comment
Question by:mamelas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38801439
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 38801517
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 38801636
...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
 

Author Closing Comment

by:mamelas
ID: 38806068
That's what I was looking for. Thank you very much for your help.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question