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)

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Response[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]Product3[/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Product2[/TD]
[TD="align: center"]Product5[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Product6[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Product3[/TD]
[TD="align: center"]Product8[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Product4[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]Product6[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Product2[/TD]
[TD="align: center"]Product7[/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]Product8[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Product9[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1 (atfer calculation)

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Response[/TD]
[TD="align: center"]Result[/TD]
[TD="align: center"]Result[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]Product3[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Product2[/TD]
[TD="align: center"]Product5[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Product6[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Product3[/TD]
[TD="align: center"]Product8[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Product4[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]Product6[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Product2[/TD]
[TD="align: center"]Product7[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]Product8[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Product9[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[/TR]
</tbody>[/TABLE]

​

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.