# Thread: Summing a range of Values (Access 2000)

1. ## Summing a range of Values (Access 2000)

<img src=/S/doh.gif border=0 alt=doh width=15 height=15>

Good Morning,

B1=1 D1=6/25 E4=#Value! (but should =2)
B2=0
B3=1
B4=0 D4=6/26

Below is code to determine the total of B1..B3 and input the value in E1.
I am not including B4 in determing the value because D4 has a different date than the rest.

E1 has the following formula
=IF(B1<>"",SumForDate(B1),"")

Code:
Function SumForDate(rng As Range) As Long
Application.Volatile
End Function

However, E4 continues to return "#Value!" rather than the total which should be "2".

Should I be referencing "D" because that's the cell the date resides in? If so, how should I change the code to Sum the values in the range between the date differences which is "B1..B3"?

Can someone help me resolve this issue?

2. ## Re: Summing a range of Values (Access 2000)

Roberta,

Is this similar to your previous scenario, in that the Date cell is blank (D, in this case) until a change of date occurs ?

Andrew C

3. ## Re: Summing a range of Values (Access 2000)

Andrew,

Yes this is the same scenerio. Still designing this spreadsheet so the user has as little input as possible. I want to know the sum of the cells in "B" for each date...once the date changes then the summing starts over for that period.

4. ## Re: Summing a range of Values (Access 2000)

Will this do what you want?

<pre>Function SumForDate(rng As Range) As Long
Application.Volatile
Dim I As Integer
SumForDate = rng.Value
I = 1
Do While (rng.Offset(I, 2) = "") And (rng.Offset(I, 0) <> "")
SumForDate = SumForDate + rng.Offset(I, 0)
I = I + 1
Loop
End Function</pre>

5. ## Re: Summing a range of Values (Access 2000)

Coleman,

I tested your suggestion and put the following formula in E:

=IF(D1<>"",SumForDate(b1),"")

and still it returns a #VALUE!...do you have another suggestion? I would appreciate it.

Thanks

6. ## Re: Summing a range of Values (Access 2000)

I'm not sure what you did, but I am attaching the workbook I used to test this. It works fine for me.

7. ## Re: Summing a range of Values (Access 2000)

I just had another thought. After putting my code in the workbook, did you do anything to cause the formulas to recalculate?

8. ## Re: Summing a range of Values (Access 2000)

Coleman,

OK...i found out what i did wrong and corrected it and it works..EXCEPT...in the case of the following

B1=1 D1=6/25 E1=2 (but should =3)
B2=1
B3=""
B4=1
B5=1 D5=6/26

I think the reason is the OFFSET...once the offset determines a null value it stops summing...I put a 0 in B3 and still it quit summing at B2.

Again I thank you for your help and welcome another suggestion.

9. ## Re: Summing a range of Values (Access 2000)

That is because the code is written to stop if the cells in column B and D are both blank. I did that so you could use the function in E5. The following will work for E1, but will fail if you put it in E5:

<pre>Function SumForDate(rng As Range) As Long
Application.Volatile
Dim I As Integer
SumForDate = rng.Value
I = 1
Do While rng.Offset(I, 2) = ""
SumForDate = SumForDate + rng.Offset(I, 0)
I = I + 1
Loop
End Function
</pre>

If you need to have empty cells in Column B and use the function for the last date group, then you need to find another way to tell the function when to stop.

10. ## Re: Summing a range of Values (Access 2000)

Legare,

Thanks for your help, I found out the problem was in the Offset. The scenerio I posted was just that a scenerio...it wasn't the actual layout of my spreadsheet. Once I made the changes to the Offset..it worked.

i.e. the column count between the Value and the Date in the actual spreadsheet is 10, so I changed the offset to 10 and everything is fine.

My apologies is this threw u off course. I do appreciate your help.

Thanks

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•