How to find consecutive 3 days and 2 days particular customer is red

Hi Team,

I need to find consecutive 3 days and 2 days for a particular customer if it red for any of the 3 metrics. Once I filter 2 days it should filter 2 days and if 2 days it should filter 3 days.

Where there is not data in any metric tag as blank.

Hi,
you can use a pivot for each metrix like in the attached and a formula to find the 2-match:

in J5
=IFERROR(IF(OR(AND(L5=L4,M5=M4+1),AND(L5=L6,M5=M6-1)),"2-MATCH","No Match"),"")

and the 3-match
K5 =IF(AND(L5=L4,M4=M5-1,L5=L6,M6=M5+1),"3-MATCH","")

Sriku,

You have been given a solution by Belinda. It may not be what you want, but you have not commented on it and said why is does not work. Similarly you are asked to provide a model answer of what you expect, so people know what is trying to be created. I can not see that as well. Help yourself by trying to be a bit more prescriptive to people who are trying to help you.

And here is the formula to count the overall red customers:
=COUNT(ROW($B$1:$B$2179)/((MATCH($B$3:$B$2179,$B$3:$B$2179,0)=ROW($B$1:$B$2179))*($L$3:$L$2179="yes")))

you can either do it with a pivot table like I show on column "N".

Thanks Belinda for your response

You're welcome. Will be happy to know if this was helpful...