IF, AND, OR conditions in the same formula

[FM1]

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"

 

[mikerickson]

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

 

[dave2018]

In C1:

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

 

[Finalfight40]

How About:

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

 

[FM1]

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]

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]

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]

You can shorten that to:

=--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:

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

 

[mikerickson]

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]

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.