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 work

Any ideas??

✔ Best Answer

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.

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.

Sorry for the confusion. 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

Hope this is more clear

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.

A B C 0 2 0 1 0 0 0 1 2 SUM 0This is an example of Column A. Column B is where the sums are calculated

Since you didn't include Row numbers in your example, I can only assume that your data is in A1:A10. Based on what you've shown, here is the obvious solution to sum the adjacent cell and the 3 non zero values above it in column A (That's what you asked for, right?)

=SUM(A1:A9)

I seriously doubt that that is what you are looking for, but you still haven't made it clear, at least to me, what you are trying to do. Keep in mind that we can't see your spreadsheet from where we're sitting, nor can we read minds.

My suggestion works for what you have posted, but I have no idea if it will work for anything else since I can only work with what is shown in your example and the words you use in your posts.

Make believe that you are one of us, sitting out here on the web, having no idea what you are trying to do. After you write your post, read it as if you are one of us and try to determine if it will be absolutely clear to someone who is unfamiliar with your task.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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+A11Forgive 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 before

I have written in Column B the cells the formula should add.

Hope it is clear this time

If the cells have a Zero in them, then SUM A2+A4+A7+A8

is equivalent to

SUM(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?

MIKE

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 editor

2 - Click Insert, Choose Module

3 - Paste the following code into the pane that opens

4 - To use the UDF in a cell, enter =SumFour(cell reference) wherecell referenceis 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 detailwhat 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.

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+ENTER

Many thanks in any case for assistance

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.

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History