count blank cells to next value

[Malika]

Hello,
I am using Excel 2007. Column A records individual sales. Column C records
the date that bulk payments are made to suppliers. When a date is entered
into Column C, I would like Column B to sum the value in Column A up to the
previous bulk payment. For example, when the date is entered into C7, B7
should sum A7:A2.

A B C
1 14.40 14.40 15/03/2010
2 9.00
3 9.00
4 9.00
5 9.00
6 9.00
7 9.00 54.00 25/03/2010

Any assistance would be appreciated.

 

[Faraz Ahmed Qureshi]

On B2 try:
=if(ISNUMBER(C2),sum(A$1:A2)-sum(B$1:B1),"")
and copy it down.
Best of luck!

 

[Bob Phillips]

Try this ARRAY formula

=IF(C1="","",SUM(A1:INDEX(A:A,MAX(1,MIN(IF($C$1:$C1="",ROW($B$1:$B1)))))))

 

[Don Guillett]

Right click sheet tab>view code>insert this.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Or Target.Count > 1 Then Exit Sub
If Target.Row = 1 Then
Target.Offset(, -1) = Cells(1, 1)
Else
fc = Target.End(xlUp).Row + 1
Target.Offset(, -1) = _
Application.Sum(Range(Cells(fc, 1), Cells(Target.Row, 1)))
End If
End Sub

 

[Malika]

Hello Faraz,

Many, many thanks for sharing your knowledge. Your suggestion worked
perfectly. Much appreciated.

Malika

 

[Malika]

Hello Bob,

Thank-you very much for your suggestion. Although I already have a solution
(provided by Faraz), I was keen to try your suggestion as well. What I found
though was that the formula totals all the values in Column A (i.e. if I
enter a date in C7, B7 totals A1:A7, if there is a date in C7 and I enter a
date in C8, B8 totals A1:A8 instead of just A8.)

While it is not the solution for the problem I posted, it is a solution I
was looking for in another workbook. So thank-you very much.

Malika

 

[Malika]

Hello Don,

Many, many thanks for your advice. The code works beautifully on my current
worksheet. However, if I could ask for a bit more help please.

I tried to adapt it to another worksheet in which the data was in columns H,
I and J, by changing Target.Column <> 10. But it didn't work correctly. I
obviously need to do more but am not sure what. I would appreciate your
further advice.

Kind regards

Malika

 

[Don Guillett]

Need to change the constants from ,1 to target.column-2. Look in help for
CELLS

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Or Target.Count > 1 Then Exit Sub
col1=target.column-2
If Target.Row = 1 Then
Target.Offset(, -1) = Cells(col1, 1)
Else
fc = Target.End(xlUp).Row + 1
Target.Offset(, -1) = _
Application.Sum(Range(Cells(fc, col1), Cells(Target.Row, col1)))
End If
End Sub