Need Help with Modifying an IF/OR Statement to add an AND condition

Dianna G

I have a column I recently created in a sheet to help our team know if they are missing information in a critical field before they move on to approvals (Critical Fields). I used an IF / OR statement to look at all of the critical fields and determine if any of them are empty. If one is empty then the field (checkbox) will be checked.

The formula works, but now I have another condition I need to work into the formula and I'm stuck.

This is my current formula (it's working), but I need to add the new condition:

=IF(OR(ISBLANK(Supplier@row), ISBLANK([Business Units Impacted]@row), ISBLANK([Estimated TOTAL Spend for Contract Term]@row), ISBLANK([Estimated ANNUAL Spend]@row), ISBLANK([Contractual Commitment Spend]@row), ISBLANK([Term Length]@row), ISBLANK([Commercial Summary for Approvals]@row), ISBLANK([Contractual Risks]@row), ISBLANK([Contractual Benefits]@row), ISBLANK([Payment Terms]@row), ISBLANK([Termination Rights]@row)), 1, 0)

I now need the formula to look at two things:

1) If the [Executed Short Form]@row is blank

AND

2) If any of the critical fields are blank

The result I am seeking: If both 1 and 2 are Yes then the checkbox should be checked. But, if the Executed Short Form field is checked then the [Critical Fields] box should not be checked. How should I write this formula? I am stumped trying to write an If / OR / AND statement and wondering if there is a better way to write this...

Carson Penticuff

You could do this by combining AND/OR, but I think it is easier to nest your existing formula inside an extra IF statement. Give this a try:

=IF(ISBLANK([Executed Short Form]@row), IF(OR(ISBLANK(Supplier@row), ISBLANK([Business Units Impacted]@row), ISBLANK([Estimated TOTAL Spend for Contract Term]@row), ISBLANK([Estimated ANNUAL Spend]@row), ISBLANK([Contractual Commitment Spend]@row), ISBLANK([Term Length]@row), ISBLANK([Commercial Summary for Approvals]@row), ISBLANK([Contractual Risks]@row), ISBLANK([Contractual Benefits]@row), ISBLANK([Payment Terms]@row), ISBLANK([Termination Rights]@row)), 1), 0)

Dianna G

That didn't work. Somehow it turned off all of the checked boxes in the column.

Dianna G

Basically, if it is an executed short form, then the empty critical fields do not matter so the box should not be checked. If it's not an executed short form, then the critical fields matter and the box should be checked if one of them is blank.

Linda Manduchova

Hi @Dianna G -

I don't see how Carson's nested formula is not working but you can try the following one to see if that helps:

=IF(AND(ISBLANK([Executed Short Form]@row), OR(ISBLANK(Supplier@row), ISBLANK([Business Units Impacted]@row), ISBLANK([Estimated TOTAL Spend for Contract Term]@row), ISBLANK([Estimated ANNUAL Spend]@row), ISBLANK([Contractual Commitment Spend]@row), ISBLANK([Term Length]@row), ISBLANK([Commercial Summary for Approvals]@row), ISBLANK([Contractual Risks]@row), ISBLANK([Contractual Benefits]@row), ISBLANK([Payment Terms]@row), ISBLANK([Termination Rights]@row))), 1, 0)

Linda

Dianna G

Still leaves all the boxes unchecked.....??? Here is my formula.

=IF(AND(ISBLANK([Executed Short Form]@row), OR(ISBLANK(Supplier@row), ISBLANK([Business Units Impacted]@row), ISBLANK([Estimated TOTAL Spend for Contract Term]@row), ISBLANK([Estimated ANNUAL Spend]@row), ISBLANK([Contractual Commitment Spend]@row), ISBLANK([Term Length]@row), ISBLANK([Commercial Summary for Approvals]@row), ISBLANK([Contractual Risks]@row), ISBLANK([Contractual Benefits]@row), ISBLANK([Payment Terms]@row), ISBLANK([Termination Rights]@row))), 1, 0)

Dianna G

I figured it out! Since the Executed Short Form column is a checkbox, the formula didn't seem to like the ISBLANK condition. I changed it to look at if it =0 and it worked.

=IF(AND([Executed Short Form]@row = 0, OR(ISBLANK(Supplier@row), ISBLANK([Business Units Impacted]@row), ISBLANK([Estimated TOTAL Spend for Contract Term]@row), ISBLANK([Estimated ANNUAL Spend]@row), ISBLANK([Contractual Commitment Spend]@row), ISBLANK([Term Length]@row), ISBLANK([Commercial Summary for Approvals]@row), ISBLANK([Contractual Risks]@row), ISBLANK([Contractual Benefits]@row), ISBLANK([Payment Terms]@row), ISBLANK([Termination Rights]@row))), 1, 0)

Carson Penticuff

That makes sense, I didn't realize [Executed Short Form] was a checkbox. I'm glad it's working now!