Formula to count consecutive responses - text in two columns with response in one column

[smide]

Hello.


In columns A and B (A2:A500 and B2:B500) I have a list of product names (text cells) and in column C (in the same row) their response.
Response in column C (C2:C500) is also text, a binary variable: Y (Yes) or N (No).


I need a formula to COUNT consecutive N (No) responses for each product's previous appearances and to place results in the columns D or E in the same row where referent product appears.
If referent product is in column A then result should be in column D and if referent product is in column B result should be placed in column E.


example.


Example only for Product1 here.

Sheet1 (stored data)

	A	B	C
1			Response
2	Product1	Product3	Y
3	Product2	Product5	N
4	Product6	Product1	N
5	Product3	Product8	N
6	Product4	Product1	Y
7	Product1	Product6	N
8	Product2	Product7	Y
9	Product1	Product8	N
10	Product9	Product1	Y
11	...	...	...

<tbody>
</tbody>

Sheet1 (atfer calculation)

	A	B	C	D	E
1			Response	Result	Result
2	Product1	Product3	Y
3	Product2	Product5	N
4	Product6	Product1	N		0
5	Product3	Product8	N
6	Product4	Product1	Y		1
7	Product1	Product6	N	0
8	Product2	Product7	Y
9	Product1	Product8	N	1
10	Product9	Product1	Y		2
11	...	...	...	...	...

<tbody>
</tbody>

​

Calculation/counting explained (response Y - STOP counting!):


- E4 = 0 , previously Product1 appeared only in row2 (response Y)
- E6 = 1 , previously Product1 appeared in row4 (response N) and row2 (response Y)
- D7 = 0 , previously Product1 appeared in row6 (response Y)
- D9 = 1 , previously Product1 appeared in row7 (response N) and row6 (response Y)
- E10 = 2 , previously Product1 appeared in row9 (response N) and row7 (response N) and row6 (response Y)

 

[inactiveuserps07]

@smide, what may look at first like a simple thing really isn't, but rather requires a lot of conditional logic and "last match" finagling.

That said, given your sample data set, enter the following formula into D2:

=IF(A2="Product1",IF(COUNTIF($A$2:$B2,"Product1")=1,"",IF(INDEX($C$1:$C1,SUMPRODUCT(MAX((($A$1:$A1="Product1")+($B$1:$B1="Product1"))*ROW($C$1:$C1))))="Y", 0,SUM(INDIRECT("D"&SUMPRODUCT(MAX((($A$1:$A1="Product1")+($B$1:$B1="Product1"))*ROW($C$1:$C1)))&":E"&ROW()-1))+1)),"")

Drag-copy the formula to E2. It will modify all relative references.

Then select D2 and E2, and drag-copy down Columns D and E.