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

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

try this idea where col g is your col 1
=SUM(INDIRECT("g1:g"&MATCH(1,I:I,0)))

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> 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
>

One way ..

Assuming this data is in Sheet1,
cols A and B, from row1 down

> 1 200 0
> 2 300 0
> 4 500 1
> 6 400 0
> 7 300 0
> 9 600 0

In Sheet2
------------
Put in A1:

=SUM(OFFSET(Sheet1!$C:$C,,COLUMNS($A$1:A1)-3,MATCH(1,Sheet1!$C:$C,0)))

Put in A2: =SUM(Sheet1!A:A)-A1

Select A1:A2, copy across to B2

A1:B2 will return:

7 1000 (< sum of values until (& inclusive) the flag "1" in col C)
22 1300 (< sum of values after the flag "1" in col C)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
<[email protected]> wrote in message
news:[email protected]...
> 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
>