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 work

Any ideas??


See More: sum variable range in excel

Report •

✔ Best Answer
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 row

Will 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, 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


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
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.


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 before

I 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 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

http://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 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) 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+ENTER

Many thanks in any case for assistance


Report •

#10
January 22, 2013 at 08:16:41
✔ 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.


Report •

Ask Question