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

4 Replies
best response confirmed by Dinesh0911 (Occasional Contributor)
Solution

# Re: Help on Excel formula

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.

# Re: Help on Excel formula

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

# Re: Help on Excel formula

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

# Re: Help on Excel formula

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"))))