asked on

Excel if coumn N = 1 and Column O = 0 then....

Hi All,

Im tryign to search a colum to find out if it contains a value.

My theroy is to seach column N to see if it contains "1" if it does search Column O to see if anything the 1 contians a 0 in column O

I tried this but throws an error...

=IF(Result!N2:N1000=”1”,(If(Result!o2:o1000=”0”,Yes,No))

Open in new window

Example would be
*************
N O
1 1
1 4
1 1
2 0
********
The above would return no as N contains no 0's IN column O where coumn N = 1

*************
N O
1 1
1 0
1 1
2 0
********
The above would return yes as N contains no a 0 IN column O where coumn N = 1

Saqib Husain

Try

=sumproduct((n2:n1000=1)*(o2:o1000=0)*1)

Saqib Husain

If you have 2007 or later then you can also try

=COUNTIFS(O2:O1000,0,N2:N1000,35)

That should give you the number of lines found. If you just need a yes or no then try

=if(COUNTIFS(O2:O1000,0,N2:N1000,35),"Yes","No")

Angelp1ay

I think your 35 should be a 1??

Saqib Husain

You are right

TAOSA-Admin

IF(logical test, value if true, value if false)

=IF1(logical test1 Result!N2:N1000=”1”,value if true1(If2(logical test 2Result!o2:o1000=”0”,value if true 2 Yes,value if false 2 No), value if false1)

You appear to have no value if false for the outer IF statement and a missing trailing close parenthesis. Every left (open parenthesis) shoudl have a matching right (close parenthesis). You have three lefts and two rights.

Member_2_5230414

ASKER

I did =IF(COUNTIFS(O2:O1000,0,N2:N1000,1),"Yes","No")

but it says no when it should say yes!!!

Aaatched is the sheet
J.P.B.S.xlsx

ASKER CERTIFIED SOLUTION

Angelp1ay

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial