# Complex Multiple Condition If Then

#### Aimee S.

##### Board Regular
Here's an example of what my table looks like with labels being Columns N, O, P, & Q. Column Q is where I want my new formula with your help:

_17_ | ____ | ____ | ____
_13_ | _15_ | _15_ | ____
____ | _11_ | ____ | ____
_81_ | _52_ | ____ | ____

I need a formula in column Q that does the following:

If there is a value in Column P, disregard all other columns and pull the value in Column P into Column Q.

If there is NO value in Column P, but there IS a value in Column N and/or Column O, pull the greater of all available values in Column N and/or O into Column Q.

If no values are present in any of Columns N, O or P, make Column Q show "Eng Not Complete". ### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### lenze

##### Legend
Are the values numeric?? Can they be negative? zero?

lenze

#### Aimee S.

##### Board Regular
The values are actually dates in dd/mm/yy format. I simplifed to numbers for the purpose of this thread. Excel as you know figures out great or less than for dates the same as numbers.

There are no zeros as to how I have set this up. Only blanks where applicable. And no negative numbers, or dates as the case may be #### RSXchin

##### Well-known Member
i think this'll work
Code:
``=IF(AND(ISBLANK(N1),ISBLANK(O1),ISBLANK(P1)),"Eng Not Complete",IF(ISBLANK(P1),MAX(N1,O1),P1))``

#### Aimee S.

##### Board Regular

i think this'll work
Code:
``=IF(AND(ISBLANK(N1),ISBLANK(O1),ISBLANK(P1)),"Eng Not Complete",IF(ISBLANK(P1),MAX(N1,O1),P1))``

Thanks, but this formula is not pulling any values whatsoever if Column P has no data and there is a value in either Column N and/or Column O. It does appear to return a blank value if all three columns are blank, or the value in Column P if one is present...

Oh, and the Eng Not Complete thing is not showing correctly if all three cells are blank.. Last edited:

#### Haseeb Avarakkan

##### Well-known Member
Hello, Try

Q2, copy down...

=IF(N(N2),N2,IF(COUNT(O2:P2),MAX(O2:P2),"Eng Not Complete"))

#### RSXchin

##### Well-known Member

did you make sure to change all the 1's to whatever row you put it in to begin with?

##### MrExcel MVP
Here's an example of what my table looks like with labels being Columns N, O, P, & Q. Column Q is where I want my new formula with your help:

_17_ | ____ | ____ | ____
_13_ | _15_ | _15_ | ____
____ | _11_ | ____ | ____
_81_ | _52_ | ____ | ____

I need a formula in column Q that does the following:

If there is a value in Column P, disregard all other columns and pull the value in Column P into Column Q.

If there is NO value in Column P, but there IS a value in Column N and/or Column O, pull the greater of all available values in Column N and/or O into Column Q.

If no values are present in any of Columns N, O or P, make Column Q show "Eng Not Complete". Try...

Q2:

=IF(ISNUMBER(P2),P2,IF(COUNT(N2,O2),MAX(N2,O2),"Eng Not Complete"))

#### Aimee S.

##### Board Regular
Both Aladin and Haseeb's solutions work. Very elegant! Thanks!

So to wrap up my day, I would need to just grab this in formula form:

If column V contains a value, and column Q shows "Eng Not Complete", make column R show "CCRD But No EP".
If however column V does NOT contain a value, simply pull value from Column Q into Column R.

##### MrExcel MVP
Both Aladin and Haseeb's solutions work. Very elegant! Thanks!

Great. Thanks for providing feedback.

So to wrap up my day, I would need to just grab this in formula form:

If column V contains a value, and column Q shows "Eng Not Complete", make column R show "CCRD But No EP".
If however column V does NOT contain a value, simply pull value from Column Q into Column R.

Looks like...

R2:
Code:
``````=IF(V2<>"",
IF(Q2="Eng Not Complete","CCRD But No EP",""),
Q2)``````

Replies
19
Views
166
Replies
3
Views
45
Replies
6
Views
44
Replies
4
Views
53
Replies
1
Views
23