Though the logic appears valid and it yields the expected results I am not particularly confident of this one.
Array entered in K2 / filled down.
=SUM(IFERROR(((TRANSPOSE(C2:H2)={"MIAOUT","MIA","UPTO"})+(-1*(TRANSPOSE(D2:H2)={"","<>DAYOFF","<>HOLOFF"}))),FALSE))+1
Edit: TRANSPOSE was unnecessary. Loose ends from the build. My bad. This instead.
=SUM(IFERROR(((C2:H2={"MIAOUT";"MIA";"UPTO"})+(-1*(D2:H2={"";"<>DAYOFF";"<>HOLOFF"}))),FALSE))+1
Row\Col A B C D E F G H I J K 1Name Max Consecutive Days 1-Jul 2-Jul 3-Jul 4-Jul 6-Jul 7-Jul Expected Results in column B 2Bob Jones 6MIAOUT DAYOFF MIAOUT MIAOUT MIAOUT MIAOUT 5 5 3John Smith 6MIAOUT MIAOUT MIAOUT DAYOFF MIAOUT MIAOUT 5 5 4Bill Joe 6HOLOFF MIA MIA MIA MIA MIA 5 5 5George Washington 3HOLOFF UPTO DAYOFF UPTO MIA 2 2 6Thomas Jefferson 6UPTO UPTO UPTO DAYOFF UPTO UPTO 5 5 7Ben Franklin 6MIAOUT DAYOFF MIAOUT HOLOFF MIA MIA 4 4 8Sam Adams 6DAYOFF UPTO UPTO HOLOFF UPTO UPTO 4 4 9John Hamilton 5DAYOFF UPTO HOLOFF UPTO UPTO 3 3
Bookmarks