Limited Sumif

Hello all,
I am looking for a kind of Sumif function to sum the values in column A if
the conditions in column B are OK. BUT the range should be limited. Sumif
sums all the values in the range which match a condition, while I am looking
for some function which limits the range (as specified). In the example
below, when you drop down from C1, the values from column A should be summed
in column C once you reach the first '1' for all B's=0, but only for the
previous 'block' of zeros. I hope the example makes it a bit more clear.
A B C
0,1 0 0
0,1 0 0
0,1 0 0
0,1 1 0,3 (=sum A1-A3)
0,1 1 0
0,1 1 0
0,1 0 0
0,1 0 0
0,1 0 0
0,1 0 0
0,1 1 0,4 (=sum A7-A10)
....
thanks!
Maarten

Let A1:B15 house:

{"X",1;
0.1,0;
0.1,0;
0.1,0;
0.1,1;
0.1,1;
0.1,1;
0.1,0;
0.1,0;
0.1,0;
0.1,0;
0.1,1;
0.1,1;
0.1,0;
0.1,0}

On you system, a value like 0.1 is 0,1.
Note that A1:B1 houses: X and 1. X is a label, while 1 is required.
Suppose that you have a label in B1, like Y. Replace Y with 1 and custom
format B1 as:

[=1]"Y";General

Now B1 will display Y but it will house 1, which is needed by the
formula in C2 that follows...

C2:

=IF((B1=0)*(B2=1),SUM(A1:INDEX($A$1:A1,MATCH(2,1/($B$1:B1=1))+1)),"")

which you need to confirm with control+shift+enter (instead of just with
enter) and copy down.

Maarten wrote:
> Hello all,
> I am looking for a kind of Sumif function to sum the values in column A if
> the conditions in column B are OK. BUT the range should be limited. Sumif
> sums all the values in the range which match a condition, while I am looking
> for some function which limits the range (as specified). In the example
> below, when you drop down from C1, the values from column A should be summed
> in column C once you reach the first '1' for all B's=0, but only for the
> previous 'block' of zeros. I hope the example makes it a bit more clear.
> A B C
> 0,1 0 0
> 0,1 0 0
> 0,1 0 0
> 0,1 1 0,3 (=sum A1-A3)
> 0,1 1 0
> 0,1 1 0
> 0,1 0 0
> 0,1 0 0
> 0,1 0 0
> 0,1 0 0
> 0,1 1 0,4 (=sum A7-A10)
> ...
> thanks!
> Maarten

It works fine, Thanks a lot!

"Aladin Akyurek" wrote:

> Let A1:B15 house:
>
> {"X",1;
> 0.1,0;
> 0.1,0;
> 0.1,0;
> 0.1,1;
> 0.1,1;
> 0.1,1;
> 0.1,0;
> 0.1,0;
> 0.1,0;
> 0.1,0;
> 0.1,1;
> 0.1,1;
> 0.1,0;
> 0.1,0}
>
> On you system, a value like 0.1 is 0,1.
> Note that A1:B1 houses: X and 1. X is a label, while 1 is required.
> Suppose that you have a label in B1, like Y. Replace Y with 1 and custom
> format B1 as:
>
> [=1]"Y";General
>
> Now B1 will display Y but it will house 1, which is needed by the
> formula in C2 that follows...
>
> C2:
>
> =IF((B1=0)*(B2=1),SUM(A1:INDEX($A$1:A1,MATCH(2,1/($B$1:B1=1))+1)),"")
>
> which you need to confirm with control+shift+enter (instead of just with
> enter) and copy down.
>
> Maarten wrote:
> > Hello all,
> > I am looking for a kind of Sumif function to sum the values in column A if
> > the conditions in column B are OK. BUT the range should be limited. Sumif
> > sums all the values in the range which match a condition, while I am looking
> > for some function which limits the range (as specified). In the example
> > below, when you drop down from C1, the values from column A should be summed
> > in column C once you reach the first '1' for all B's=0, but only for the
> > previous 'block' of zeros. I hope the example makes it a bit more clear.
> > A B C
> > 0,1 0 0
> > 0,1 0 0
> > 0,1 0 0
> > 0,1 1 0,3 (=sum A1-A3)
> > 0,1 1 0
> > 0,1 1 0
> > 0,1 0 0
> > 0,1 0 0
> > 0,1 0 0
> > 0,1 0 0
> > 0,1 1 0,4 (=sum A7-A10)
> > ...
> > thanks!
> > Maarten
>