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