Forum Discussion
Solved
Help on Excel formula
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 an...
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.
Thank you so much HansVogelaar, it is working as I expected.
HansVogelaar, 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"?
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"))))