Formula for repetitive data per row

[katamata]

Hello,
I'm looking for a formula in column G that returns YES or NO when YES is repeated more than once in columns A to F, but the previous and next columns are NO, as described in rows 2 and 4 in the example.
Thanks in advance!
Be healthy!

[davsth]

I do not understand what you are asking from the sample file can you try to explain more?

[katamata]

If in columns 1 to 6 YES is repeated more than once and if before and after YES there is NO the expected result is YES otherwise it is NO.
or
If before and after NO there is YES the expected result is YES otherwise it is NO.
I supplemented the example with two new lines.

[Bo_Ry]

Please try

=IF(COUNT(FIND({12,21},NPV(9,1+(A2:F2="NO"))))=2,"YES","NO")

[davsth]

why is row 9 yes? when there is not a no before and after? I have to confess the formula is beyond me and a very clever solution, which I will have to sit down and work out!

[katamata]

Look above:
"or
If before and after NO there is YES the expected result is YES otherwise it is NO."
Thank you!
I need one report, the columns are the twelve months, but the principle for the other 6 is the same.

[bebo021999]

Try in G2:

Please Login or Register  to view this content.

[katamata]

Thank you so much!
Only one equal (=) after the greater (>) and it works perfectly.
IF(COUNT(SEARCH({"NOYESNO","YESNOYES"},A2&B2&C2&D2&E2&F2))>=1,"YES","NO")
Be healthy!

[Bo_Ry]

why is row 9 yes? when there is not a no before and after? I have to confess the formula is beyond me and a very clever solution, which I will have to sit down and work out!

@davsth Thanks for kind comment and the reps

Before I came up with
=IF(COUNT(FIND({12,21},NPV(9,1+(A2:F2="NO"))))=2,"YES","NO")
I used
=IF(COUNT(SEARCH({"OY","SN"},A2&B2&C2&D2&E2&F2))=2,"YES","NO")

But if we want to expand to more column like A:Z then we need to add 25 more & ampersand which is not fun
for MS 365 we can use Concat(A2:Z2) but other vesion don't have this.

NPV trick to concat number, I learn from XOR LX
https://www.excelforum.com/excel-for...ml#post5378511
I have Thai Clip to explain NPV
https://youtu.be/dbTH590_RDg


1+(A2:F2="NO") Change Yes to 1 and NO to 2
We can concat number from A:Z using just

NPV(9,1+(A2:Z2="NO")))

instead of

A2&B2&C2&D2&E2&F2…&Y2&Z2

Then find 12 for YesNO and 21 for NoYes