Forum Discussion

Copper Contributor

Sep 02, 2021

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

Excel formula.xlsx13 KB

Formulas and Functions

HansVogelaar
Sep 02, 2021
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.
Excel formula.xlsx13 KB

HansVogelaar

MVP

Sep 02, 2021

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.

Excel formula.xlsx13 KB

Dinesh0911

Copper Contributor

Sep 02, 2021

Thank you so much HansVogelaar, it is working as I expected.

Dinesh0911
Copper Contributor
Sep 03, 2021
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"?
- HansVogelaar
  MVP
  Sep 03, 2021
  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"))))