Count consecutive missing values in columns

[smide]

Hello.

In columns A and B (A3:B802) I have a list of 25 different products (Product 1 - Product 25) with their corresponding 'markers' in columns C and D (when Product is in column A his 'marker' is in the same row in column C, and when Product is in column B his 'marker' is in the same row but now in column D).
Each Product's marker can have a value of zero, one or three (0,1,3).

What I need to find is to COUNT how many consecutive times each Product appears without marker 3 relative to last Product appearance eg. referent cell (columns A or B).

Data in row 3 are actually the last one (from which counting starts).

example. (for simplicity here only for Product 1)

Results are in column E.

	A	B	C	D	E
1
2			Marker A	Marker B	Results
3	Product 1	Product 3	1		0
4	Product 4	Product 1		3	2
5	Product 3	Product 6
6	Product 1	Product 4	0		1
7	Product 7	Product 1		1	0
8	Product 8	Product 6
9	Product 1	Product 2	3		....
10	....	....
11	....	.....

<tbody>
</tbody>

Results: E3 = 0, because next Product 1 (B4) marker (D4) already contains number 3

E4 = 2, because there are two Product 1 markers less than 3 before marker with number 3 for Product 1 appears in row 9

E6 = 1, there is only one Product 1 marker less than 3 before marker with number 3 for Product 1 appears in row 9

E7 = 0, similar story as for E3

 

[Eric W]

I'm sure this can be simplified, but here's a first stab at it:

	A	B	C	D	E	F	G
1							Product
2			Marker A	Marker B	Results		Product 1
3	Product 1	Product 3	1		0
4	Product 4	Product 1		3	2
5	Product 3	Product 6
6	Product 1	Product 4	0		1
7	Product 7	Product 1		1	0
8	Product 8	Product 6
9	Product 1	Product 2	3		0
10

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula E3 {=IF(COUNTIF($A3:$B3,$G$2),SUM(IF(($A4:$B15=$G$2)*ISNUMBER($C4:$D15)*(ROW($A4:$B15)< IFERROR(1/(1/MIN(IF(($A4:$B15=$G$2)*($C4:$D15=3),ROW($A4:$A15)))),ROW($A$15))),1)),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

I used 15 as the maximum row of your range, which you'll need to change.