IF, AND, OR conditions in the same formula

FM1

Board Regular
Joined
Jan 1, 2008
Messages
61
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"
 

Some videos you may like

Excel Facts

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

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,779
Try
=IF(AND(LEN(B1)>0, OR(A1="In Progress",A1="Unassigned")), "1 value", "other")
 
Last edited:

Finalfight40

Active Member
Joined
Apr 24, 2018
Messages
273
Office Version
  1. 365
Platform
  1. Windows
How About:

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

FM1

Board Regular
Joined
Jan 1, 2008
Messages
61

ADVERTISEMENT

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
Joined
Apr 24, 2018
Messages
273
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 1, 2008
Messages
61

ADVERTISEMENT

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
Joined
Feb 3, 2010
Messages
9,682
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 15, 2007
Messages
23,779
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
Joined
Jan 1, 2008
Messages
61
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,655
Messages
5,524,131
Members
409,561
Latest member
ay123

This Week's Hot Topics

Top