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

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

2. Saji,

Welcome to the lounge as a new poster!

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

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

4. 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. Saji,

I highlighted P7:V10 and V3:V6, then right-clicked and selected Clear Contents.
TestCase.JPG

#### Posting Permissions

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