Need Help with Modifying an IF/OR Statement to add an AND condition
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...
Answers
-
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)
-
That didn't work. Somehow it turned off all of the checked boxes in the column.
-
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.
-
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
-
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)
-
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)
-
That makes sense, I didn't realize [Executed Short Form] was a checkbox. I'm glad it's working now!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!