# Formula Help

Solved/Closed
Shaun - Apr 8, 2010 at 08:32 AM
Shaun - Apr 8, 2010 at 02:14 PM
I'm trying to create a formula to look at the values in a complete row and if 3 of those values in a row are pass then it will fill in the cell with Yes.

A B C D E F G H I
PASS PASS FAIL PASS PASS PASS

For instance in the above example this row has indicating whether they pass or fail a work area and column I will indicate whether they are certified or not based on if they have 3 passes in a row. A would be their first work area, B would be the second and so on, so I also need for the formula in column I to keep looking at A-H as they progress so I wouldn't show a Yes until it is looking at cells D-F having 3 passes in a row, but if they were to Fail in column G then the Yes would either disappear or change to No. Is there any way to create a formula like this?
Thanks

## 8 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 8, 2010 at 11:48 AM
Based on you clarification

IF(MAX(IF(D1&E1&F1&G1&H1<>"",0,1) * COUNTIF(A1:C1,"PASS"),IF(E1&F1&G1&H1<>"",0,1) * COUNTIF(B1:D1,"PASS"),IF(F1&G1&H1<>"",0,1) * COUNTIF(C1:E1,"PASS"),IF(G1&H1<>"",0,1) * COUNTIF(D1:F1,"PASS"),IF(H1<>"",0,1) * COUNTIF(E1:G1,"PASS"),COUNTIF(F1:H1,"PASS"))=3,"PASS","FAIL?")

This formula looks for data in row 1 columns A-H. It looks for last three block that are filled. If that block has thRee PASS, then it display pass ELSE IT WILL SHOW FAIL?
Thank you very much, that worked great. A couple small things I still need to try to see about fine tuning but it's a lot farther than I would have been and the fine tuning isn't entirely necessary. Thanks again for all your help.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 8, 2010 at 08:53 AM
Try this

=IF(MAX(COUNTIF(A1:C1,"PASS"),COUNTIF(B1:D1,"PASS"),COUNTIF(C1:E1,"PASS"),COUNTIF(D1:F1,"PASS"),COUNTIF(E1:G1,"PASS"),COUNTIF(F1:H1,"PASS"))=3,"PASS","FAIL?")

This formula looks for data in row 1 columns A-H. If there is a block of three cells that show "PASS" then formula will show pass. If there are no such three cell block, formula will show "FAIL?".
Hi Shaun,

I didn't fully understand your detailed explanation, but if you want to display a Yes when Pass appears a least 3 times use these formula's:

In cell J1: =SUM(IF(A1:H1="PASS",1,0)) confirm formula by hitting Ctrl+Shift+Enter.
In cell I1: =IF(J1>=3,"Yes","No")

The first formula is an array formula and will count how many times the value Pass is present in the range A1:H1. You can either hide the column or change the textcolor to white if you do not wish to see the result.
The second formula looks at the number and either puts a Yes or a No in cell I1.

Best regards,
Trowa
Thanks, it's close but in the first formula provided I don't want it to look at any block of 3 in the row to give a yes if there are 3 passes in a row.

In the second formula again it is calculating every pass in that row.

What I would like it to do is always look at the last 3 cells in that row that have entries in them and see if those last 3 have 3 consecutive passes and if they do then a yes is put in at the end, but if the next cell ends up being a Fail then the end cell will automatically switch to NO, then in order for them to get it back to Yes then the next 3 cells would need to be filled in with Pass before it changes back to Yes.

Didn't find the answer you are looking for?