Compound IF statement in Excel

Bill Golden
Bill Golden used Ask the Experts™
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
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011
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 Member


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.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial