If And Contains Formula

Nathan Umbriac

I'm trying to auto-populate a date that references another column +90 calendar days when another column is true and includes any countries in a list, otherwise, the result will be 'Not applicable.'

=IF(AND([Report]@row = "Corporate Report", ([Assigned Countries]@row CONTAINS("Austria, Belgium...etc"), [Due Date ]@row + 90, "Not applicable")))

David Tutwiler

What part of this doesn't work? Does it come back as unparseable or does it not react like you expected?

Nathan Umbriac

Thank you, David. The formula as written gives me 'Not Applicable', even when the conditions are true.

Nic Larsen

There's likely an easier way (index/match?) to reference the multiple countries, but this was working for me. You'd just have to keep expanding the OR part to include any that need to be included:

=IF(AND(OR(CONTAINS("Austria", [Assigned Countries]@row), CONTAINS("Denmark", [Assigned Countries]@row)), Report@row = "Corporate Report"), [Due Date]@row + 90, "Not Applicable")

Nathan Umbriac

Thanks Nic. I wrote out the formula including each country, but get unparseable:

=IF(AND(OR(CONTAINS("Austria", [Assigned Countries]@row), CONTAINS("Belgium", [Assigned Countries]@row), CONTAINS("Bulgaria", [Assigned Countries]@row), CONTAINS("Croatia", [Assigned Countries]@row), CONTAINS("Cyprus", [Assigned Countries]@row), CONTAINS("Czech Republic", [Assigned Countries]@row), CONTAINS("Denmark", [Assigned Countries]@row), CONTAINS("Estonia", [Assigned Countries]@row), CONTAINS("Finland", [Assigned Countries]@row), CONTAINS("France", [Assigned Countries]@row), CONTAINS("Germany", [Assigned Countries]@row), CONTAINS("Greece", [Assigned Countries]@row), CONTAINS("Hungary", [Assigned Countries]@row), CONTAINS("Iceland", [Assigned Countries]@row), CONTAINS("Ireland", [Assigned Countries]@row), CONTAINS("Italy", [Assigned Countries]@row), CONTAINS("Latvia", [Assigned Countries]@row)CONTAINS("Liechtenstein", [Assigned Countries]@row), CONTAINS("Lithuania", [Assigned Countries]@row), CONTAINS("Luxembourg", [Assigned Countries]@row), CONTAINS("Malta", [Assigned Countries]@row), CONTAINS("Netherlands", [Assigned Countries]@row), CONTAINS("Norway", [Assigned Countries]@row), CONTAINS("Poland", [Assigned Countries]@row), CONTAINS("Portugal", [Assigned Countries]@row), CONTAINS("Romania", [Assigned Countries]@row), CONTAINS("Slovakia", [Assigned Countries]@row), CONTAINS("Slovenia", [Assigned Countries]@row), CONTAINS("Spain", [Assigned Countries]@row), CONTAINS("Sweden", [Assigned Countries]@row)), [Type & Category of Report]@row = "Corporate Report"), [Due Date to HA]@row + 90, "Not Applicable")

Genevieve P.

Hi @Nathan Umbriac

You're missing a comma between Latvia and Liechtenstein:

CONTAINS("Latvia", [Assigned Countries]@row)CONTAINS("Liechtenstein", [Assigned Countries]@row), 

Update it to:

CONTAINS("Latvia", [Assigned Countries]@row), CONTAINS("Liechtenstein", [Assigned Countries]@row), 

And you should be good to go!

Cheers,

Genevieve

Nathan Umbriac

Thank you, Genevieve. I'm still getting Not Applicable for all rows.

Nic Larsen

I could not find the error in your formula. I retyped it though and was not getting an error so try copy and pasting this one. It was functioning on my sheet. The column type needs to be a Date column.

=IF(AND(OR(CONTAINS("Spain", [Assigned Countries]@row), CONTAINS("Sweden", [Assigned Countries]@row), CONTAINS("Norway", [Assigned Countries]@row), CONTAINS("Poland", [Assigned Countries]@row), CONTAINS("Portugal", [Assigned Countries]@row), CONTAINS("Romania", [Assigned Countries]@row), CONTAINS("Slovakia", [Assigned Countries]@row), CONTAINS("Luxembourg", [Assigned Countries]@row), CONTAINS("Malta", [Assigned Countries]@row), CONTAINS("Netherlands", [Assigned Countries]@row), CONTAINS("Slovenia", [Assigned Countries]@row), CONTAINS("Latvia", [Assigned Countries]@row), CONTAINS("Liechtenstein", [Assigned Countries]@row), CONTAINS("Lithuania", [Assigned Countries]@row), CONTAINS("Iceland", [Assigned Countries]@row), CONTAINS("Ireland", [Assigned Countries]@row), CONTAINS("Italy", [Assigned Countries]@row), CONTAINS("Germany", [Assigned Countries]@row), CONTAINS("Greece", [Assigned Countries]@row), CONTAINS("Hungary", [Assigned Countries]@row), CONTAINS("Estonia", [Assigned Countries]@row), CONTAINS("Finland", [Assigned Countries]@row), CONTAINS("France", [Assigned Countries]@row), CONTAINS("Austria", [Assigned Countries]@row), CONTAINS("Belgium", [Assigned Countries]@row), CONTAINS("Bulgaria", [Assigned Countries]@row), CONTAINS("Croatia", [Assigned Countries]@row), CONTAINS("Cyprus", [Assigned Countries]@row), CONTAINS("Czech Republic", [Assigned Countries]@row), CONTAINS("Denmark", [Assigned Countries]@row)), [Type & Category of Report]@row = "Corporate Report"), [Due Date to HA]@row + 90, "Not Applicable")

Nathan Umbriac

The formula is working, but the only result I get is 'Not Applicable.' Shouldn't I get a date when the formula is true?

Genevieve P.

Hi @Nathan Umbriac

Yes, you should get a date, but only if there's one of the places that you're looking for in the "Assigned Countries" column and there's "Corporate Report" in your other column. Is it possible that there's a difference in spelling or structure in the [Type & Category of Report] column?

If possible, it would be really helpful to see a screen capture of your sheet, but please block out any sensitive data.

Cheers,

Genevieve

Nathan Umbriac

Thanks @Genevieve P, you were correct that I was referencing the wrong structure in the [Type & Category of Report] column. Thanks again!!

Genevieve P.

Glad you got it working! 🙂