# Solved sum variable range in excel

January 20, 2013 at 02:42:29
Specs: Windows 7
 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 B 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 bove that row.Have tried using SUM and OFFSET but it doesnt workAny ideas??

See More: sum variable range in excel

January 22, 2013 at 08:16:41
 Interesting. I see that this only works when placed in Column B next to a non-zero value in Column A. My UDF can be used anywhere in the sheet if that helps in any way. In other words, you can place it in C47 and reference A19 to get the 4 numerical values above A19.A couple of other issues: 1 - Depending on how complex your worksheet is, a large number of array formulas could slow it down. If you are dragging this down Column B, there might be an impact of performance.2 - I noticed that Text entries in Column A will cause a problem. It looks to me like it counts the Text as one of the 4 cells to Sum, but obviously can't sum them. I don't know if that will be an issue for you. If so, my UDF ignores Text and keeps going until it finds 4 numerical values.Good luck!Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

#1
January 20, 2013 at 06:07:12
 There's some parts of your question that aren't clear to me.I think we need a short example of your data and the expected outcome based on that example.re: "I want to create a formula in Column B where in case the value of the Column B for the specific row is non zero,"I'm confused as to how you can have a formula in Column B when there are also values in Column B.re: produce the sum of that cell in Column A plus the three non zero cells of column A above that rowWill the three non zero cells in Column A be contiguous (e.g A7:A9) or not (e.g. A6, A9, A14)?Please click on the following line and follow the instructions found via that link before posting your example data.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
January 21, 2013 at 03:58:26
 Sorry for the confusion.Column A contains the values, the non zero being e.g. A6, A9, ,A10, A14Column 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+A10Hope this is more clear

Report •

#3
January 21, 2013 at 06:56:58
 Thanks, however I'm still confused. Can you please post an example of your data as requested? I don't know how this line fits into to situation:"...column B for the specific row is non zero"It sounds like you want to put a formula in a cell that already has a value in it.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

#4
January 21, 2013 at 07:13:49
 A B C 0 2 0 1 0 0 0 1 2 SUM 0 This is an example of Column A. Column B is where the sums are calculated

Report •

#5
January 21, 2013 at 07:42:00

Report •

#6
January 21, 2013 at 08:04:48
 A B 1 0 2 1 3 0 4 2 5 0 6 0 7 3 8 4 SUM A2+A4+A7+A8 9 0 10 6 SUM A4+A7+A8+A10 11 4 SUM A7+A8+A10+A11 Forgive my lack of clarity, it is the first time I am using this forum and need to get the hang of it.I hope this is more clear than beforeI have written in Column B the cells the formula should add.Hope it is clear this time

Report •

#7
January 21, 2013 at 08:22:20
 If the cells have a Zero in them, then SUM A2+A4+A7+A8 is equivalent toSUM(A2:A8)But why do you have your sums on rows 8, 10, 11?Are you summing by some type of a Four Count?What is special about those specific rows?MIKEhttp://www.skeptic.com/

Report •

#8
January 21, 2013 at 17:04:16
 There is no built in Excel function that can "look upward", find 4 non zero values and SUM them.A User Defined Function (UDF) written in VBA is required.I wrote this and it seems to work.1 - Use Alt-F11 to open the VBA editor2 - Click Insert, Choose Module3 - Paste the following code into the pane that opens4 - To use the UDF in a cell, enter =SumFour(cell reference) where cell reference is the cell where you want to start looking up from.e.g. Based on your example, =SumFour(A10) will SUM A4, A7, A8, A10 5 - You must save the workbook as .xlsm in order for the UDF to be saved.Note: It's not possible for me to test for every situation or every different type of user input. All I can suggest is that you try the Function and if it returns unexpected results, please explain in detail what you did that caused those results.Function SumFour(ByVal myRng As Range) Application.Volatile 'Loop through rows in descending order For myRow = myRng.Row To 1 Step -1 'If non-zero found, sum and increment counter If IsNumeric(Cells(myRow, myRng.Column)) And _ Cells(myRow, myRng.Column) <> 0 Then tempSum = tempSum + Cells(myRow, myRng.Column) sumCount = sumCount + 1 'Stop when 4 values Summed If sumCount = 4 Then GoTo sum4 End If Next sum4: 'If 4 values were not found, present Message If sumCount < 4 Then SumFour = "4 Values Not Available" Exit Function End If 'If 4 values were found, present Sum SumFour = tempSum End Function Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#9
January 22, 2013 at 07:25:27
 Didn,t try this but this is something that worked well=IF(A6=0,"",IFERROR(SUM(IF(ROW(A\$6:A6)>=LARGE(IF(A\$6:A6<>0,ROW(A\$6:A6)),4),A\$6:A6)),"")).....confirmed with CTRL+SHIFT+ENTERMany thanks in any case for assistance

Report •

#10
January 22, 2013 at 08:16:41