Return user define value using IF & OR

atisyam

New Member
Joined
Sep 19, 2018
Messages
37
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"))
 

Some videos you may like

Excel Facts

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

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
3,954
Office Version
  1. 365
Platform
  1. MacOS
=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
Joined
Sep 19, 2018
Messages
37
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

Well-known Member
Joined
Oct 24, 2012
Messages
3,954
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,109,492
Messages
5,529,176
Members
409,854
Latest member
rickcoba
Top