Forum Discussion
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 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.
HansVogelaar , kindly help on this.
Thanks in advance.
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.
4 Replies
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"?
