# Return user define value using IF & OR

#### atisyam

##### New Member
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.

Code:
``=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"))``

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### etaf

##### Well-known Member
=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

##### New Member
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.

you are welcome

Replies
4
Views
41
Replies
9
Views
58
Replies
2
Views
272
Replies
3
Views
63
Replies
5
Views
49