Forum Discussion

Dinesh0911's avatar
Dinesh0911
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...
Formulas and Functions
  • HansVogelaar's avatar
    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.

HansVogelaar's avatar
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.

  • Dinesh0911's avatar
    Dinesh0911
    Copper Contributor
    Sep 02, 2021
    Thank you so much HansVogelaar, it is working as I expected.
    • Dinesh0911's avatar
      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's avatar
        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"))))

Resources