I have column A and every 3 rows I want to check to see if the value "Fail" exists in there.

I have this, but I need to figure out the function to set the range to every 3 rows to check for the value:

=IF(ISNUMBER(SEARCH(A2:A4,"Fail")),"Fail","Pass")

the A2:A4 is the 3 row range that I need to check every segment (e.g. A5:A7, A8:A10). If any one of the 3 cells in that range has the word "Fail" then I need to label all three cells in the formula column "Fail"

any ideas?

CodePudding user response：

Something like this with COUNTIFS, INDEX, ROW, and MOD:

=IF(COUNTIFS(INDEX(A:A,ROW()-MOD(ROW() 1,3)):INDEX(A:A,ROW() 2-MOD(ROW() 1,3)),"Fail"),"Fail","Pass")