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

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	...	...	...

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	...	...	...	...	...

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)

Originally Posted by AliGW

What about all the other products?

Yes, I need 'counting' for ALL products. In example I calculated only for Product1 for simplicity.

I don't understand what you are trying to do here - what is the data meant to show?

Guess I'll have to wait for someone who do understand what I'm trying to do.
Thanks for reply anyway.

Originally Posted by AliGW

Please would you add your expected results for ALL products? Thanks.

Sheet1 (atfer calculation) - ALL products

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

If you see Glenn's reply, then you will see the need for columns per product.

See attached using OP's formula:

Originally Posted by JohnTopley

We then have to repeat the exercise for "Product2", "Product6" etc .
These set of tests will need to be in "product-unique" columns OR maybe use VBA for a two column solution.

What do you think about using "R00C00" as some unique reference for all product's when counting?
I mean something like: {=IFERROR(INDIRECT(TEXT(LARGE(IF($A$1:$B1=A2,ROW($A$1:$B1)*100+COLUMN($C$1:$D1)),3),"R00C00"),0)+...
This is just an idea/formula example of course...

Originally Posted by JohnTopley

If you see Glenn's reply, then you will see the need for columns per product.

See attached using OP's formula:

Unfortunately I can not allow in my project additional columns, columns per product etc.
However I really appreciate your effort. Thank you.

If you don't find a formula solution, then it would be possible to create a VBA macro which would loop through products and assign (change) the product referenced in the formula so would still have your 2 columns.

If VBA is acceptable, ask a moderator to re-assign this thread to the VBA/Macro forum.