How do I use an IF AND OR formula based on specific text criteria and return back a TRUE or FALSE?

Question

I'm trying to use a IF AND OR formula and for some reason I can't get it to work correctly. The formula isn't returning any error message but I know it's not working correctly based on the criteria I have filled in. Any idea why this isn't working for me?

Attached is a screenshot of the excel sheet and data and listed below is the current formula I am using.

=IF(AND(B:B="Active",OR(C:C="",D:D="")),"TRUE","FALSE")

Right now the formula is returning back each row as "FALSE" but based on the sample data I have I know that Rows 4, 5, 7, and 10 should have "TRUE" displayed because the status of column B is "Active" and at least one of Columns C and D (or both) are blank for all those rows.

Any help would be appreciated!

Answer 1

AND and OR don't return an array, but a single TRUE/FALSE value.

You could use

=IF((B:B="Active")*((C:C="")+(D:D="")), TRUE)

But I'd prefer to refer to the table instead of referring to entire columns.

In E4:

=AND([@Status]="Active", OR([@Planner]="", [@[Project Manager]]=""))

This will add a column to the table.

Answer 2

The issue that you are having is that AND and OR each only return one TRUE or FALSE result. They will not return a spilled result. So, they will not compare individual rows as the formula needs. To use AND and OR they will need to refer to individual rows and then be filled down.

=IF(AND(B4="Active",OR(C4="",D4="")),"TRUE","FALSE")

Actually, to use them you don't need the IF function either since the AND function already produces TRUE or FALSE. See Column H below.

=AND(B4="Active",OR(C4="",D4=""))

Answer 3

Ok thanks that worked, but now I am trying to add this formula under another tab but each time I click into the tab to select B4 or anything else it just selects the same cell but under the 2nd tab where I'm typing out the formula. Is there a way to add this logic under a different tab while referencing the first tab where the data is?

Answer 4

Let's say the sheet with the data is named Sheet 1.

=AND('Sheet 1'!B4="Active",OR('Sheet 1'!C4="",'Sheet 1'!D4=""))

Answer 5

To refer to cells on a different sheet from where you are entering the formula, as you enter the formula when you need it to reference the other sheet, click on the other sheet and select the cell you want (B4) on that sheet, and that will insert the sheet name for the other sheet where the data is located. It will look like this with your sheet name instead of Sheet1. (Or you can use a sheet named Sheet1 and use this formula and then later change the name of the sheet and the formula will update the sheet name when you change it.)

=AND(Sheet1!B4="Active",OR(Sheet1!C4="",Sheet1!D4=""))