Compound IF statement in Excel

I need to test the existence of an X in various cells in another worksheet.  

If 'Balance Sheet'!P221 contains an X, then return the value of 'Balance Sheet!B288 + 1, but if P221 is blank...
If 'Balance Sheet'!P148 contains an X, then return the value of 'Balance Sheet!B215 + 1, but if P148 is blank...
If 'Balance Sheet'!P75 contains an X, then return the value of 'Balance Sheet!B142 + 1, but if P75 is blank...
If 'Balance Sheet'!P2 contains an X, then return the value of 'Balance Sheet!B69 + 1, but if P2 is blank...
return the value of Setup!D53
LVL 1
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Rory ArchibaldCommented:
That would be:

=If('Balance Sheet'!P221="X",'Balance Sheet!B288+1,if('Balance Sheet'!P148="X",'Balance Sheet!B215+1,If('Balance Sheet'!P75="X",'Balance Sheet!B142+1,If('Balance Sheet'!P2="X",'Balance Sheet!B69+1,Setup!D53))))

assuming you mean the cells in column P should contain only"X". If they could contain other text as well, the formula will need adjusting.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
scsymeCommented:
The basic pattern you're after is as follows, modelled on the first case of your question.
=IF(IFERROR(SEARCH("X",'Balance Sheet'!P221),0)>0,'Balance Sheet'!B288 + 1, "P211 is blank")

Open in new window


A couple of clarifications: are you looking to nest all of your statements above, i.e. the second case applies if P221 is blank and so on for the other cases?

Does the contains check need to be case sensitive? SEARCH, which I have used above is not case sensitive. You can use FIND as a direct replacement if the check does need to be case sensitive.
0
Bill GoldenExecutive Managing MemberAuthor Commented:
That got the job done.  There was a " ' " or two missing after the Balance Sheet reference but the previous answer allowed me to spot the problem.  Thanks.

Bill
0
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 Excel

From novice to tech pro — start learning today.