IF AND and an OR nested together?

[Ron Purpura]

Hello All
I am pretty sure this is possible, but cant quite get my formula to behave how I am expecting

Towards the end of the very long formula in AA43 I would expect the total of the conditions being added to equate to 8 and not 7 as returned. I am certain it is how I have my "sub" formula at the end of the long formula.... IF(AND(OR(X43="Y",AB43="N"),X43="N",AB43>0),1,0)

I am looking to return a 1 if the either of these 2 conditions are met in the sub formula:

If V43 =Y AND Z43 = N then return a 1 OR if V43=N AND X43>0 then return a 1

Can someone tell me where I went wrong?

Thanks so much

[Sam Capricci]

well what you described in this section

If V43 =Y AND Z43 = N then return a 1 OR if V43=N AND X43>0 then return a 1

makes me think this is how that part should look...
=IF(OR(AND(V43="y",Z43="n"),AND(V43="n",X43 > 0)),1,0)

[Ron Purpura]

Thanks so much Sambo Kid. I dont think I explained myself well enough. The formula you graciously provided is actually adding 2 instead of 1 as I would expect
and I am sure it is because I am not getting across what I am looking for so please accept my apology

If either one of the conditions exist, I wish to add a 1 to the formula:

If V43 =Y AND Z43 = N add 1
OR
V43=N AND X43>0 add 1

Any ideas?

Thanks again

[Sam Capricci]

well if I do that as a stand alone formula in your sheet it returns 1, not 2.
if I substitute that portion into your formula to replace this...
IF(AND(OR(V43="Y",Z43="N"),V43="N",Z43 > 0),1,0)
with this...
IF(OR(AND(V43="y",Z43="n"),AND(V43="n",X43 > 0)),1,0)
and leave the rest alone it returns 8 which is what you posted in your original post as what you want the result to be.

[Ron Purpura]

Gotcha ! Thanks again !

[Sam Capricci]

thank you for the rep!

[FDibbins]

The bolded part doesnt look right?
=SUM(IF(
OR(S43="N",S43="N/A"),1,0)+IF(
OR(T43="N",T43="N/A"),1,0))+IF(
OR(U43="N",U43="N/A"),1,0)+IF(
OR(V43="y",V43="N/A",V43="N"),1,0)+IF(
OR(W43>0,),1,0)+IF(
OR(X43>0,X43="N/A",V43="N"),1,0)+IF(
OR(Y43>0,Y43="N/A",V43="N"),1,0)+IF(AND(
OR(V43="Y",Z43="N"),V43="N",Z43>0),1,0)+IF(AF43="N",1,0)
You are testing for V43 = both Y and N?

[FDibbins]

If the answer in AA43, then maybe this shorter version...
=COUNT(
OR(S43="N",S43="N/A"),
OR(T43="N",T43="N/A"),
OR(U43="N",U43="N/A"),
OR(V43="y",V43="N/A",V43="N"),
W43>0,
OR(X43>0,X43="N/A",V43="N"),
OR(Y43>0,Y43="N/A",V43="N"),
OR(V43="Y",Z43="N",V43="N",Z43>0),
AF43="N")

[Sam Capricci]

Ford,

OR(V43="Y",Z43="N"),V43="N",Z43>0),1,0)+IF(AF43="N",1,0)
You are testing for V43 = both Y and N?

That is the part I made the recommendation to change.
But I didn't read it enough to parse out a shorter more efficient version as you did in post 8.

[FDibbins]

Sambo, I kinda figured you had, but that is a pretty long, involved formula, and I was working through it while you posted

[Ron Purpura]

Thank you both FDibbins and Sambo Kid for spending time on my formula.
I sorta lost you Mr Dibbins..Are you suggesting to replace the formula I have in AA43 with the formula you wrote? Yes, I agree it is LONG ha ha , but that is a reflection of my limited abilities with Excel
Yes, I am basically testing for a Y or N in V43
Thank you again

[FDibbins]

Yes, that is what Im suggesting. However, if V43 can only be Y or N, you only need 1 test, if it is Y, then by default it cannot be N (and vise versa)
So you need to sort that part out 1st

[Ron Purpura]

Makes sense ! Thanks again

[FDibbins]

Happy to help and thanks for the feedback