# AND OR formula

Hi,
I would like some help in modifying this formula
Logic .......   col B must = BOY     col C = 3 or col D = 5 or col E = 7

=IF(AND(OR(C2=3,D2=5,E2=11),AND(B2="BOY"),99),0)
RETURN = 99

Many thanks
Ian
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
HI,

pls try
``````=IF(AND(OR(C2=3,D2=5,E2=7),AND(B2="BOY"),99),0)
``````
Regards
2
Commented:
Try:

``````=IF(AND(OR(C2=3,D2=5,E2=7),B2="BOY"),99,0)
``````

»bp
1

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Was distracted... Bill's probably right
1
Excel & VBA ExpertCommented:
I know it has been resolved by Bill, another one is like this... :)
``````=IF(AND(SUM(C2:E2)=16,B2="BOY"),99, 0)
``````
0
Commented:
Hmmm,

The problem with a SUM approach is that 1,1,14 also sum to 16, but wouldn't meet the criteria, right?

»bp
1
Excel & VBA ExpertCommented:
Yes Bill. I realized soon after posting that I cannot rely on Sum formula. :)
0
Finance AnalystCommented:
And 3 + 5 +7 equals 15 anyway!!!
0
Excel & VBA ExpertCommented:
In that case the formula should be this...

``````=IF(AND(OR(C2=3,D2=5,E2=11 ),B2="BOY"),99, 0)
``````
E2 is 11 not 7 as per the formula shown in the description. :)
0
Commented:
Well, depends on if the sample formula, or "logic" text was right.  Maybe it's a trick question :)

Naturally my response in #a42396889 above can be adjusted to which ever is right, hopefully it showed the right way to AND() and OR() the needed conditions...

»bp
0
Finance AnalystCommented:
Still wouldn't sum to 16, would be 19. ;-)

And its an OR for those 3 cells anyway, only one needs to match for the OR to return TRUE.
0
retiredAuthor Commented:
That worked nicely Bill
And thanks to all who contributed.
0
Excel & VBA ExpertCommented:
Lol... You are right Rob.
0
Excel & VBA ExpertCommented:
@ Ian
We all enjoyed the participation. :)
1
retiredAuthor Commented:
The question was not a trick question :)  any one of the correct values in col's  C,D or E would work.
1
retiredAuthor Commented:
Thanks Neeraj, it was a bit of fun :)   but got the job done.
0
Finance AnalystCommented:
Another alternative:

=IF(OR(B2&C2="Boy3",B2&D2="Boy5",B2&D2="Boy11"),99,0)
1
Commented:
Yes, this one was fun, beers at my house later :)

»bp
0
retiredAuthor Commented:
Rob I think you may have misunderstood the sample I gave.
The 11 in COL E.. I threw in was a red herring. The rules were fulfilled when
Col C had a 3 and for good measure Col D had a 5 value.
as per following
Logic .......   col B must = BOY     col C = 3 or col D = 5 or col E = 7

Hope that clears things
0
retiredAuthor Commented:
I'm up for it Bill,  :)
0
Rob was trying to write this:
``````=IF(OR(B2&C2="Boy3",B2&D2="Boy5",B2&E2="Boy7"),99,0)
``````
0
retiredAuthor Commented:
Yes that also worked...... well done Ramin for spotting it and to Rob for his effort
which proves there's more than one way to skin a banana :)
1
Commented:
And for what it's worth, I'd probably reverse the AND() conditions, like below.  Let it check the simple = condition before the more complex OR condition.  There is a tiny chance that could be a wee bit faster, if Excel is smart enough to stop evaluating the multiple pieces of the AND as soon as it hits the first false one.  So maybe when BOY is not matched, it doesn't waste time doing the three OR comparisons...

``````=IF(AND(B2="BOY",OR(C2=3,D2=5,E2=7)),99,0)
``````

»bp
1
retiredAuthor Commented:
Anything to save time and works well
Thanks Bill, those beers will go down well :)
1
Finance AnalystCommented:
Thank you Ramin. Yes, it was a typo.

Sadly as one of the few UK representatives on EE, it is too far to come for Bill's beer.
2
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.