Formula HelpSolved/Closed

Question

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

rizvisa1 · Accepted Answer

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?

rizvisa1 · Answer

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?".

Trowa · Answer

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

Shaun · Answer

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.

rizvisa1 · Answer

So are you saying is that in A-H, you are only interested in last 3 filled cells and that will determine if there is a yes or a no ?

Shaun · Answer

Correct, but i want to be able to do so without needing to revise the formula each time to specifically look at the last 3 and be able to keep the yes or no to populate in that same end cell.

rizvisa1 · Answer

Ok clarify one more thing. Will there be an empty cell in between. ? Like
A1, B1, C1, E1 are filled but D1 is not filled ?

Shaun · Answer

No, should not have any empty cells in between.   A1, B1, C1, D1, and E1 should all have either Pass or Fail in them, F1 could have another random value like "QQ" in it and all following cells would be empty.  If the "QQ" can be ignored that would be great, it can be left blank if that is not possible.

Formula Help

8 responses

Similar discussions

Subscribe To Our Newsletter!