Return user define value using IF & OR

[atisyam]

Hello. I am comparing 2 different workbooks. I want this formula to return a "P" if the cell values in the 2nd workbook (Trial31) contains "Pass/Pass" or "Pass/Fail". Else if none of these 2, return an "O".

I'm also using COUNTIFS as apart from the above, I also need to check if another set of cell values from

the 2nd workbook​

(Trial31) contains matching data in cell D1 of my 1st workbook.

The formula below returns an error. Hope to get help. Thanks.

=IF((COUNTIFS('[Trial31.xlsx]Sheet1 '!$B$2:$B$10,D1,'[Trial31.xlsx]Sheet1 '!$K$2:$K$10,"Pass/Fail") OR (COUNTIFS('[Trial31.xlsx]Sheet1 '!$B$2:$B$10,D1,'[Trial31.xlsx]Sheet1 '!$K$2:$K$10,"Pass/Pass"))>0,"P","O"))

 

[etaf]

=IF((COUNTIFS('[Trial31.xlsx]Sheet1 '!$B$2:$B$10,D1,'[Trial31.xlsx]Sheet1 '!$K$2:$K$10,"Pass/Fail") OR (COUNTIFS('[Trial31.xlsx]Sheet1 '!$B$2:$B$10,D1,'[Trial31.xlsx]Sheet1 '!$K$2:$K$10,"Pass/Pass"))>0,"P","O"))

(COUNTIFS('[Trial31.xlsx]Sheet1 '!$B$2:$B$10,D1,'[Trial31.xlsx]Sheet1 '!$K$2:$K$10,"Pass/Fail") OR (COUNTIFS('[Trial31.xlsx]Sheet1 '!$B$2:$B$10,D1,'[Trial31.xlsx]Sheet1 '!$K$2:$K$10,"Pass/Pass"))>0

you need to use , assuming the counts are OK

OR (COUNTIFS('[Trial31.xlsx]Sheet1 '!$B$2:$B$10,D1,'[Trial31.xlsx]Sheet1 '!$K$2:$K$10,"Pass/Fail")>0 , COUNTIFS('[Trial31.xlsx]Sheet1 '!$B$2:$B$10,D1,'[Trial31.xlsx]Sheet1 '!$K$2:$K$10,"Pass/Pass")>0)

i also notice a space between sheet1 and the !

You are also counting a different workbook , is that in the sme directory and is it open or closed ?


IF ( OR (COUNTIFS('[Trial31.xlsx]Sheet1 '!$B$2:$B$10,D1,'[Trial31.xlsx]Sheet1 '!$K$2:$K$10,"Pass/Fail")>0 , COUNTIFS('[Trial31.xlsx]Sheet1 '!$B$2:$B$10,D1,'[Trial31.xlsx]Sheet1 '!$K$2:$K$10,"Pass/Pass")>0) , "P","O")

 

[atisyam]

Hi Wayne

Thanks for your help. Both the workbooks are from the same directory (Desktop) and both are opened, side by side.

The last formula worked perfectly. Thanks again.

 

[etaf]

you are welcome