Help on Excel formula

[Dinesh0911]

Hello Everyone,

Greeting for the day..!

I need help on creating the formula on excel for the below scenario, Kindly help me.

Scenario: 

In sheet 1, we have a test case and corresponding status and depends on the status of those steps in one column we have to determine the final status and provide the same in the sheet2.

Conditions:

if all the steps are passed-->it is considered as PASS and should be given as PASS

if at least one step is failed-->it is considered as fail test case in sheet2

if there are combination of only Pass and In progress->It is considered as INPROGRESS status in sheet2

if there are combination of only Pass and blocked->It is considered as blocked status in sheet2

Please find the sample sheet and let me know in case if you need any other information.

@Hans Vogelaar , kindly help on this. 

Thanks in advance.

[#2 · Accepted solution]

@Dinesh0911 

Fill in the test case in all rows of the Actual sheet. Otherwise, the formulas become very complicated.

In B2 on the Expected sheet:

=IF(COUNTIFS(Actual!$A:$A,$A2,Actual!D:D,"fail*"),"fail",IF(COUNTIFS(Actual!$A:$A,$A2,Actual!D:D,"block*"),"Blocked",IF(COUNTIFS(Actual!$A:$A,$A2,Actual!D:D,"in progres*"),"In Progress","Pass")))

Fill down and to the right.

[#3]

Thank you so much @Hans Vogelaar, it is working as I expected.

[#4]

@Hans Vogelaar, if all the step status are "N/A" it is being return as " Pass", Can you please help me for keeping the value as "N/A" when all the values are 'N/A"?

[#5]

@Dinesh0911 

Like this:

=IF(COUNTIFS(Actual!$A:$A,$A2,Actual!D:D,"fail*"),"Fail",IF(COUNTIFS(Actual!$A:$A,$A2,Actual!D:D,"block*"),"Blocked",IF(COUNTIFS(Actual!$A:$A,$A2,Actual!D:D,"in progres*"),"In Progress",IF(COUNTIFS(Actual!$A:$A,$A2,Actual!D:D,"Pass")=0,"N/A","Pass"))))