Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Count If to to get PASS /FAIL /NA based on values in 2 separate cols

    HI Excel Experts,

    I searched our threads for similar issue but could not find one to count from non sequential columns

    basically my problem is that - I have 2 columns - columns P and U - where I have values of PASS, FAIL or NA based on results of testing 2 separate applications.
    I have to calculate the overall PASS / FAIL in a separate column W - based on the value in the cells in the 2 columns P & U

    here are my criteria.
    If all the cells are PASS, then the overall result will be PASS
    If any one cell is FAIL, then the overall result will be FAIL
    If any one cell has a combination of NA and PASS, then it should be PASS
    If any one cell has a combination of NA and FAIL, then it should be FAIL
    If any one cell has a combination of NA, PASS and FAIL, then it should be FAIL

    I tried this formula
    IF(COUNTIF(P5:U6,"FAIL")>0,"FAIL","PASS")
    and it works correctly just for PASS or FAIL - I am unable to introduce a 3rd variable "NA"
    Also for the cells or Test Cases that I have not started - based on the formula it is showing PASS - it should be BLANK until a value of PASS, FAIL or NA is entered

    I am attaching a sample sheet - can one of you XL guru please see if you can help me out in this regards or point me in the right direction
    I really appreciate your help in this regards
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Saji,

    Welcome to the lounge as a new poster!

    As I read your description I see only 3 cases:
    1. If there is a FAIL it FAILS period!
    2. If All cells are blank the answer should be BLANK
    3. All other cases are Pass.

    One case you don't cover is if less than all cells are blank. I'm assuming that this case will not happen.

    You can solve this easily with the following User Defined Function {UDF}
    Code:
    Option Explicit
    
    Public Function zPassFail(rngScores As Range) As String
    
        Dim iScoresCount As Integer
        
        iScoresCount = rngScores.Cells.Count
        If WorksheetFunction.CountIf(rngScores, "") = iScoresCount Then
          zPassFail = ""
          Exit Function
        End If
        
        If WorksheetFunction.CountIf(rngScores, "FAIL") > 0 Then
          zPassFail = "FAIL"
          Exit Function
        End If
           
        zPassFail = "PASS"
        
    End Function
    Place the above code in a module in the workbook using the VBE {Alt+F11}. See attached workbook.

    Notes:

    1. You have to save the file as an .xlsm file {macro enabled}
    2. To enter the formula you can use the fx icon on the formula bar and select the type of User Defined in the function dialog.

    Please Note: Not all the cells in the last range were BLANK although they appeared so! I had to select them and do a clear contents. For the UDF to work they actually have to be blank!

    HTH
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi

    As an alternative to RG's macro-enabled workbook solution using UDF, you could use the attached xlsx file with modified formula in column W.

    The formula in column W will return:

    1. If any one cell is FAIL, then the overall result will be FAIL

    2. If at least one of the cells is PASS, (and no cells are FAIL) then the overall result will be PASS

    3. If cells are a combination of NA and PASS, then it should be PASS

    4. If cells are a combination of empty and PASS, then it should be PASS

    5. If cells are a combination of NA and FAIL, then it should be FAIL

    6. If cells are a combination of empty and FAIL, then it should be FAIL

    7. If all the cells are NA, then the overall result will be NA

    8. If all cells are blank, then the overall result will be NA

    (assumes hidden columns in between P and Q are irrelevant)


    zeddy
    Attached Files Attached Files

  4. #4
    New Lounger
    Join Date
    Mar 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ThanQ RetiredGeek and Zeddy - that was really helpful - I am really amazed at how much is tere to learn in XL.

    In RetiredGeek's spreadsheet - I am getting value as PASS even when I have BLANK values in other cells.
    You have specifically mentioned aNOTE - but cna you specific which cells you had to clear off - i am not getting that point of it - i am not able to find those cells

    Mr Zeddy - i was trying to achieve whatever solution you had provided thru looped IF but I could not figurte that part out - that is well put.

    Thanks a lot to both of you for your time and wonderful replies.

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Saji,

    I highlighted P7:V10 and V3:V6, then right-clicked and selected Clear Contents.
    TestCase.JPG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •