# IF, AND, OR conditions in the same formula

#### FM1

##### Board Regular
Column A = "Status". Values: Unassigned, In Progress, Rejected, Not Proceeding and Complete
Column B = "Date Closed". Values: Date

I need a statement where I get a 1 value when all the following conditions are met:

1. Date Closed is not blank
AND
2. Status = "In Progress" OR "Unassigned"

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### mikerickson

##### MrExcel MVP
Try
=IF(AND(LEN(B1)>0, OR(A1="In Progress",A1="Unassigned")), "1 value", "other")

Last edited:

#### dave2018

##### Board Regular
In C1:

=IF(AND(B1<>"",OR(A1="In Progress",A1="Unassigned")),1,0)

#### Finalfight40

##### Active Member

=IF(AND(NOT(ISBLANK(B2)), OR(A2="Unassigned", A2="in progress")), 1, 0)

Last edited:

#### FM1

##### Board Regular

Thanks for the responses everyone.

In C1:

=IF(AND(B1<>"",OR(A1="In Progress",A1="Unassigned")),1,0)

This has done the trick. Thanks!

To provide some context, I'm trying to implement some soft controls (conditional formats) whereby if the 'Date Closed' column is populated, then the 'Status' column should either be "Closed" or "Not Proceeding". If those conditions are not met, then I want to highlight a cell or have a prompt pop up requesting the user completes the form appropriately.

Is the approach I'm using (applying conditional formats based on the 1 or 0 values) the right way to do this?

#### Finalfight40

##### Active Member
Maybe on A2 (or where ever your dates start) try going into conditional formatting on the home ribbon, highlight cells rule, equal to and try the formula =not(isblank(b2)) and drag the cell down?

#### FM1

##### Board Regular

Maybe on A2 (or where ever your dates start) try going into conditional formatting on the home ribbon, highlight cells rule, equal to and try the formula =not(isblank(b2)) and drag the cell down?

It's ok for for the date field to be blank, if the status does not equal "Closed" or "Not Proceeding". So if the values are "In Progress" or "Unassigned", then it's fine for the 'Date Closed' column to be blank as the case hasn't been closed as yet.

#### JackDanIce

##### Well-known Member
You can shorten that to:
Code:
``=--AND(B1<>"",OR(A1="In Progress",A1="Unassigned"))``
-- turns TRUE into 1 and FALSE into 0, which is the same as your IF statement output when true (1) or false (0)

Consider VLOOKUP(A1,B1:D10,2,FALSE) and VLOOKUP(A1,B1:D10,2,0) both will give the same answer

I'd use part of Mike's suggestion with:

Code:
``=--AND(LEN(B1),ISNUMBER(SEARCH(A1,"In Progress|Unassigned")))``

Last edited:

#### mikerickson

##### MrExcel MVP
Thanks for the responses everyone.

This has done the trick. Thanks!

To provide some context, I'm trying to implement some soft controls (conditional formats) whereby if the 'Date Closed' column is populated, then the 'Status' column should either be "Closed" or "Not Proceeding". If those conditions are not met, then I want to highlight a cell or have a prompt pop up requesting the user completes the form appropriately.
What you are looking for is DataValidation.
Select A1:B1 and enter this custom formula for DataValidaion =OR(\$A1="",OR(\$B1="closed",\$B\$1="not proceeding")) along with appropriate messages for error and/or cell entry.
Then copy that validation to the rest of columns A and B.

#### FM1

##### Board Regular
What you are looking for is DataValidation.
Select A1:B1 and enter this custom formula for DataValidaion =OR(\$A1="",OR(\$B1="closed",\$B\$1="not proceeding")) along with appropriate messages for error and/or cell entry.
Then copy that validation to the rest of columns A and B.

This has worked really well. Thanks!

I'm not 100% sure if the data validation route will be ok with the people working on this sheet as sometimes they will need to be able to populate the data even if it is erroneous so hard controls might not be an option.

Replies
3
Views
93
Replies
0
Views
44
Replies
0
Views
55
Replies
4
Views
35
Replies
0
Views
32