IF Formula, Can it consider multiple columns?

Brianne

Good Day,

I am trying to find the best way of indicating the data within 3 columns. I thought the IF formula would do the trick. Currently I have 2 columns Unit Seal Broken (Yes/No) and then Reinventory completion. I want the formula to reflect in the Status column. see attached.

Basically what we need it to do is If the unit seal is broken (Yes), we need the status to be "re-inventory required", however when the Re-inventory Completion is entered (Date) we need the status to change to "Inventory Good"

Simultaneously we need it to also consider, if the seal is broken (No), we need status to be Inventory good, even if the date is blank.

If the Unit seal broken is blank we need the status to be blank. And also need this to apply to the entire column.

Current Formula - =IF([Unit Seal Broken?]@row = "Yes - Reinventory", "Reinventory Required", "Inventory Good")

This formula is not quite doing what we need. Thank you in advance for the assistance.

bisaacs

Hi @Brianne,

Ah sorry! I missed the paragraph in your original post about that. Then you'd probably want it to look like this:

=IF(AND([Unit Seal Broken?]@row = "Yes - Reinventory", ISBLANK([Re-inventory Completion]@row)), "Reinventory Required", IF(OR([Unit Seal Broken?]@row = "No", NOT(ISBLANK([Re-inventory Completion]@row))), "Inventory Good", ""))

That should do it :)

bisaacs

Hi @Brianne,

Yes the IF function can consider multiple columns, you can use nested IF functions to help you complete it. So in your case, it'd probably look something like this:

=IF([Unit Seal Broken?]@row = "Yes - Reinventory", "Reinventory Required", IF([Unit Seal Broken?]@row = "No", "Inventory Good", ""))

Hope this helps!

Brianne

Hi @bisaacs

This works great for the leaving it blank issue, however with the forumla you have provided, it is not considering if there is a date entered in the Second Column "Re-Inventory Completion" to change the status column to "Inventory good". Does that make sense?

bisaacs

Hi @Brianne,

Ah sorry! I missed the paragraph in your original post about that. Then you'd probably want it to look like this:

=IF(AND([Unit Seal Broken?]@row = "Yes - Reinventory", ISBLANK([Re-inventory Completion]@row)), "Reinventory Required", IF(OR([Unit Seal Broken?]@row = "No", NOT(ISBLANK([Re-inventory Completion]@row))), "Inventory Good", ""))

That should do it :)

Brianne

Wow!! You are amazing! :) Thank you.