Formula, checking a Box with multiple criteria

Jakob Henry

Hello everyone,

I have a question. I am trying to create a formula which is supposed to check a box if a specific word (lets say word1) is in column 1 and there also is a date in column 2 or if there is one of two words (lets say word2 and word3) in column 1 and a date in colum3.

So far I have this:

=IF(AND([column1@row = "Word1", ISDATE([column2]@row)), "1", IF((OR([column1]@row = "word2", [column1]@row = "word3"), ISDATE([column3]@row), "1"))

But this does not seam to work. Does anyone have any idea how to fix this?

KPH

In the first IF function, you have a missing square bracket before your first @row (shown in bold here)

=IF(AND([Column1]@row = "Word1", ISDATE([Column2]@row)), "1")

In the second IF function, you are missing the AND to combine your OR and date criteria (shown in bold here)

=IF(AND(OR([Column1]@row = "word2", [Column1]@row = "word3"), ISDATE([Column3]@row)), "1")

The full formula is

=IF(AND([Column1]@row = "Word1", ISDATE([Column2]@row)), "1", IF(AND(OR([Column1]@row = "word2", [Column1]@row = "word3"), ISDATE([Column3]@row)), "1"))

Jakob Henry

Thank you very much.

I now added the formula but I realized that I need a formula that says contain "word1", "word2" and "word3". Because there can also be other words in combination with them.

So what I have done now is that I created this formula:

But it does not seem to work. Any idea why that could be? Any help is very much appreciated.

Zachary Hall

I see a few things that could be causing an error:

1. The "p" in [column1p]@row
2. misspelled "coulmn" in the second CONTAINS clause
3. missing a parenthesis in the OR to move to the AND function
4. use of ";" instead of "," within your formula

Here is the formula without those potential errors:

• =IF(AND(CONTAINS("word1", [column1]@row), ISDATE([column2])),"1", IF(AND(OR(CONTAINS("word2",[column1]@row), CONTAINS("word3",[column1]@row)), ISDATE([column3]@row)),"1"))

Hope this helps!

Jakob Henry

First of all thank you for your answer.

1. I put the "p" in only for posting here as well as I also put all the names such as "Column1-3" and "word1-3" only in when posting here. Because I cant publish anything related to my workplace. So I had to delete the original names. But I can ensure you they are correct.
2. Same reason as above.
3. That one I fixed. Thank you very much.
4. I use the German version and that one is using ";" instead of "," but is still in English.

Still does not work.

Could it be that I have to use ISTEXT in combination with CONTAINS?

Zachary Hall

Thank you for the clarifications. You should not need an ISTEXT.

Are you getting an error message, or is it just not returning the correct results? If it is an error message, please share that.

It is hard to say without seeing specifically, but it could be something like a manually entered "date" that is actually formatted as text instead of a date value.

Jakob Henry

Hey, there is no error massage. But it should check the Box if the "word1" is in "column1" and a date is in "column2" which it doesn't.

Column2 and Column3 are formatted as date columns. Column1 is formatted as a text column. So i really don't see where the issue could be. :(

Zachary Hall

Ah! I forgot the checkbox! Remove the quotations around the 1 in the IF TRUE fields of your IF() statements. The quotes around the 1 make it want to show a text 1 rather than the boolean (converted) 1.

Jakob Henry

I did do that but it does still not work. Could it be that in column 1 it sometimes says multiple words? So it can happen that is says word1, word2 and/or word3.