CODEWITHSUNDEEP
excel
nick lanta
nick lanta

Reputation: 638

Check every 3 rows to see if a text value is in it?

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?

Upvotes: 0

Views: 106

Answers (1)

BigBen
BigBen

Reputation: 50008

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")

enter image description here

Upvotes: 1

Related Questions