SUM values in a column till you reach a flag in another column- Urgent

[naveen.vinukonda]

Hello:
My data is something like this:
Column1 Column2 Flag
1 200 0
2 300 0
4 500 1
6 400 0
7 300 0
9 600 0

I need a formula to add values in each of the first two columns till
the flag equals 1. Then another formula to add the values below the
flag seperately.

In other words- I am expecting 7, 22, 1000, 1300 values to be output
for the above sheet.

The number of rows is not constant in my worksheet although the order
of the data is standard. I would want to avoid VBA if I can do this by
a simple formula.

Please help. This is Urgent.

Thanks in advance.

-Naveen

 

[Glen Mettler]

Look at SumIF() or SumProduct()

Glen

 

[Chip]

If not, here it is in VBA:


Sub adder()

Dim intcounter As Integer
Dim valuepaster As Integer
Dim Flagchecker As Boolean
Dim lastrow As Integer

lastrow = ActiveSheet.UsedRange.Rows.Count
valuepaster = 2
intcounter = 2
sumtotal = 0

Do Until intcounter = lastrow + 2
Cells(intcounter, 2).Select
If ActiveCell.Offset(0, 1).Value = 1 Then
Flagchecker = True
End If

If Flagchecker = True Then
sumtotal = ActiveCell.Value + sumtotal
Cells(valuepaster, 4).Value = sumtotal
sumtotal = 0
valuepaster = valuepaster + 1
Flagchecker = False

Else
sumtotal = ActiveCell.Value + sumtotal
End If
intcounter = intcounter + 1
Loop
If ActiveCell.Offset(0, 1).Value = 1 Then
Exit Sub
End If
Cells(valuepaster, 4).Value = sumtotal
End Sub