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
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?
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.
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.
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.

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.

All Courses

From novice to tech pro — start learning today.