Setting a count range for specific values depending on another cell value

[spill-the-beans]

Hello all I'd like to learn more about how to use excel functions, and was wondering if anyone could help.

This is what my data looks like:[TABLE="width: 500"]
<tbody>[TR]
[TD]trial[/TD]
[TD]column B[/TD]
[TD]column C
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

I know how to do some of the counting functions I need, but the range of cells to count needs to depend on the number in the trial column.

So, in column D, I would like to write a formula that counts how many 1s there are in column B before the first 0 appears in column B. Any 1s after the first 0 should not be included in the count. Also, only include the rows that have a 1 in column A. About halfway down the table, when the number in the trial column (A) changes to a 2, I need to have a cell in column D that will count the same (how many 1s in B before the first 0 in B) but only for the rows that have a 2 in column A.

So in this example, the number of 1s before a 0 for the trial 1 range is 4 and for the trial 2 range the answer is 2.

In column E, I need to count how many 1s there are in column B for all the cells in column B that also have a 1 in column A. Against, I need to repeat this halfway down column E to count how many 1s there are in column B for the range of cells that have a 2 in column A.

So in this example, the total number of column B 1s in trial 1 is 6 and in trial 2 is 8.

(incidentally, I need to do exactly the same for column C as well as B.

If anyone could help me with even a little bit of this, it would be a massive help

 

[NBVC]

In C2:

=IF(A2<>A1,MATCH(0,B2:B$50,0)-1,"")

copied down

in E2:

=IF(A2<>A1,COUNTIFS($A$2:$A$50,A2,$B$2:$B$50,1),"")

copied down.

I assume for column C you are counting 0's to first 1?

If so, just change the 1's in the formulas to 0

Note: change bottom of range from 50 to whatever your bottom row is.

 

[spill-the-beans]

Wow - you have saved me so much time and stress - thank you so so much.

Would you also be able to tell me if it is possible in F2 to express the answer in E2 as a percent out of the total amount of cells that have a specific value in column A?

 

[NBVC]

Do you mean in F2:

=IF(E2="","",E2/COUNTIF($A$2:$A$50,$A2))

copied down?

 

[spill-the-beans]

*blinks in amazement* yes, yes I do. Thank you for sharing your knowledge - it is much appreciated!