IF Function? Multiple Columns of Data but requires one overall output

[Ozzy23]

Hi All,


I have 10 columns and 10 rows. The cells contain numbers 0-5 randomly, see a small 5X5 sample below:

	Column A	Column B	Column C	Column D	Column E	Column F (Overall Status)
Row 1	1	2	3	4	5	Blocked
Row 2	0	1	2	3	4	Fail
Row 3	5	4	3	2	1	Blocked
Row 4	3	2	1	0	2	Fail
Row 5	4	4	4	4	4	Pass

The numbers represent the following:

• 0 = Unexecuted
• 1 = Fail
• 2 = In Progress
• 3 = In Review
• 4 = Pass
• 5 = Blocked

I want to create a column at the end that summarizes these statuses, Where if any row has the following:

• If any row has a 5 the final status should be "Blocked"
• If any row has no 5 but has a 1 (Fail) then final status should be "Fail"
• If any row is All 4's (Passes) then the final status should be "Pass"
• And so on..

I was trying to do a IF statement but miserably failing and unsure of how to do a IF function when it's considering all 10 columns. Also the the logic of which status should be the overall status as 'Blocked' and 'Fail' trump other statuses.

 

[Scott Huish]

I don't know what the and so on.. refers to, but with what you've given so far

=IF(COUNTIF(A2:E2,5),"Blocked",IF(COUNTIF(A2:E2,1),"Fail",IF(COUNTIF(A2:E2,4)=5,"Pass","")))

 

[Ozzy23]

I don't know what the and so on.. refers to, but with what you've given so far

=IF(COUNTIF(A2:E2,5),"Blocked",IF(COUNTIF(A2:E2,1),"Fail",IF(COUNTIF(A2:E2,4)=5,"Pass","")))

Thank you so much Scott this worked! The and so on was just referring to additional rules I would like to implement so this worked perfectly! THANK YOU!!!