Counting the number of times a series occurs between a pattern of other cell values

[spill-the-beans]

Hello,

I was wondering if it is possible to count the occurrence of a specific series relative to other cell values. . .

My dataset has two relevant columns, G and H. The data are 1s and 0s. If G=1 then H=0 and vice versa. Alternatively, both G and H can = 0 at the same time.

What I would like to be able to do is count the number of double 0s after the first time G=1 and H=0, but before the first time G=0 and H=1.

so, the cells to count are in red below. Note that the answer should be 3 (the number of times a 0 occurs in both columns at the same time), not 6 (the number of double 0s between the criteria). Also, the very last double 0 row should not be in the count, because H has been 1 before the occurrence of that double 0.

[TABLE="width: 500"]
<tbody>[TR]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be gratefully received, thank you

 

[circledchicken]

Hi,

Perhaps try:

=COUNTIF(
     INDEX(G1:G8, MATCH(1, G1:G8, 0) + 1) :
     INDEX(G1:G8, MATCH(1, H1:H8, 0) - 1),
   0)

 

[spill-the-beans]

Yes, that works, but in column C I have a number that tells me when I need to start the count again. So in the example, every cell in column C would have been 1. While the number is still 1, that determines the range of cells to look for the first double 0.

But when the number in column C changes to 2 for ~200 cells, and then changes to 3 for ~200 cells (and so on), the count needs to begin again.

Is there a way of including that in the formula?