IF/AND/CONTAINS Formula

ChelseaH

I'm trying to gather info from another sheet and use it in an IF formula, but I've not been able to make it work correctly.

Here is the current formula:

=IF(AND(CONTAINS([38-2021]$1, {Vacations/Holidays - DDQC Range - weeks}), {Vacations/Holidays - DDQC Range - assigned} = $[DDQC Person]@row), "YES", "NO")

It returns #INVALID OPERATION

Here's another format that we tried:

=IF(OR(AND({Vacations/Holidays - DDQC Range - assigned} = $[DDQC Person]@row, {Vacations/Holidays - DDQC Range - end week} = [51-2021]$1), AND({Vacations/Holidays - DDQC Range - assigned} = $[DDQC Person]@row, {Vacations/Holidays - DDQC Range - start week} = [51-2021]$1)), "YES", "NO")

It also returns #INVALID OPERATION

I was able to make it work with a COUNTIFS formula, but I really need the yes/no option of the IF formula.

=COUNTIFS({Vacations/Holidays - DDQC Range - weeks}, CONTAINS([36-2021]$1, @cell), {Vacations/Holidays - DDQC Range - assigned}, $[DDQC Person]@row)

This is an excerpt from my vacations/holidays sheet. The ranges are for the Assigned column and Joined Weeks column.

This is where I am building the formula:

Jeff Reisman

Well, I've spent way too much time on this... Since your COUNTIFS formula works as intended, just stick it inside an IF to get your Yes/No values by checking to see if the count is greater than zero:

=IF(COUNTIFS({Vacations/Holidays - DDQC Range - weeks}, CONTAINS([36-2021]$1, @cell), {Vacations/Holidays - DDQC Range - assigned}, $[DDQC Person]@row) > 0, "Yes", "No")

Jeff Reisman

Your answer may lie in the error message.

#INVALID OPERATION

Cause

Operators (see Create and Edit Formulas for details on acceptable operators) in a formula aren't supported in Smartsheet or are mistyped. For example, this IF statement that returns the text "Low Stock" if a cell contains 25 or less...

=IF(Quantity1 =< 25, "Low Stock")

...has the "less than or equal" to operator in the wrong order; the correct order being <= to make the formula =IF(Quantity1 <= 25, "Low Stock")

Resolution

Check all operators to make sure they're not mistyped and are supported by Smartsheet. See Create and Edit Formulas for details on acceptable operators. The most common cause of this is typing <= (less than or equal to) and >= (greater than or equal to) operator combinations in the wrong order.

Since you only have one operator in your formula, that is likely the culprit.

Since your COUNTIFS formula worked, notice the second criteria in that one. Any operator there? Nope. So try formatting your criteria in your IF/AND the same way, with a comma instead of an equal sign:

=IF(AND(CONTAINS([38-2021]$1, {Vacations/Holidays - DDQC Range - weeks}), {Vacations/Holidays - DDQC Range - assigned}, $[DDQC Person]@row), "YES", "NO")

Here's a handy guide to formula error messages:

Formula Error Messages | Smartsheet Learning Center

ChelseaH

@Jeff Reisman

Thank you for the information and link to the error guide. That will definitely help me in the future!

The formatting for AND formulas require a logical expression, whereas COUNTIFS formulas require the range and criteria to be separated by commas, so unfortunately, changing the = to a , only puts my DDQC person cell reference into the AND logical expression #3, making logical expression #2 and #3 not be logical expressions.

I tried changing the reference to another cell within the sheet and the formula worked, so it seems that the cross sheet reference for Assigned is the problem. Perhaps I need another formula in that section, similar to CONTAINS, but finds the exact match instead of just containing the same data.

Going to keep poking at it. Any help is appreciated!

Jeff Reisman

Perhaps I need another formula in that section, similar to CONTAINS, but finds the exact match instead of just containing the same data.

The HAS function searches for an exact match, you may try that with an @cell reference:

https://help.smartsheet.com/function/has

=IF(AND(CONTAINS([38-2021]$1, {Vacations/Holidays - DDQC Range - weeks}), {Vacations/Holidays - DDQC Range - assigned}, HAS(@cell, $[DDQC Person]@row)), "YES", "NO")

ChelseaH

@Jeff Reisman

I wasn't aware of the HAS function. That sounds exactly like what I need to use, though it doesn't work as written.

Can you tell me the difference between:

{Vacations/Holidays - DDQC Range - assigned}, HAS(@cell, $[DDQC Person]@row)

HAS({Vacations/Holidays - DDQC Range - assigned}, $[DDQC Person]@row)

I'm not sure if there's a difference for the way the system reads the formula, but I would like to learn. I tried it both ways, but it's not returning the correct data. At least I'm not getting errors anymore! Small win.. Maybe I need to use the @cell format on the CONTAINS part as well.

The problem seems to be with the AND part of the formula. I've checked everything I know to check for, even checking that both Assigned columns do not have the multiple contacts option checked.

ChelseaH

@Paul Newcome - I know this is right up your alley! Can you help?

I've tried every combination of the formula I thought might work with no luck so far. I think I have something fundamentally wrong with the data, not the formula, but I can't imagine what could be the cause. IF(AND( should work just fine...

Jeff Reisman

https://community.smartsheet.com/discussion/comment/320302#Comment_320302

The HAS function was specifically designed to work with multi-select columns, because multiple selections are stored as independent values inside the same cell. HAS will work with other column types, but can only match an entire cell value. So if I have a cell in a Clothing text column with the words Jacket and Pants, and use =IF(HAS(Clothing:Clothing, "Jacket"), "Yes", "No") the system will return "No" because it wants to match the entire value. However, if the Clothing column is a multi-select with the values Jacket and Pants, the formula will return "Yes", because Jacket and Pants are independent values in the cell.

There also seems to be some syntax difference depending on what function you're using with HAS. For COUNTIF(S) and SUMIF(S), the @cell reference makes sense. (@cell tells the system to do the math on every cell in the column at once.) For trying to match a Contact column value, you shouldn't need it.

=IF(AND(CONTAINS([38-2021]$1, {Vacations/Holidays - DDQC Range - weeks}), HAS({Vacations/Holidays - DDQC Range - assigned}, $[DDQC Person]@row)), "YES", "NO")

So if this isn't working, there's a problem with the data or with the ranges selected.

Here's a test from my test sheet:

ChelseaH

@Jeff Reisman

I'm using a single-select contact column for the assigned on both sheets, not restricted to list values, though I did add the list values on the new sheet to keep things the same. The week/yr is a text column on both sheets. I've cleared all cross sheet references and set new ones, in case something was not set up correctly.

Using your formula, it returns YES on all people, except the one who was most recently hired and hasn't taken any vacation yet. Only 5 people on this report took time off in week 38 of 2021.

In the screenshot below, the HAS parenthesis is not colored, leading me to believe it's the culprit.

When I take out the HAS part of the formula, I get #INVALID OPERATION again and the AND parenthesis is not colored.

Ignore the "No", "#UNPARSEABLE", and "0" answers in the 38-2021 column above the formula. That's where I've been testing other variations and formula breakdowns.

Maybe I'm formatting all of this the wrong way. My intentions with this IF formula is to tack it onto the end of a SUMIFS formula that is totaling hours worked by person by week, so that I can have it noted if they were on vacation that week, making sense as to why their hours were low. This will enable the department managers to see if all employees are working time as expected. Do you know of another formula I should be using to achieve my end goal? The timesheet and vacations are on separate sheets, if that makes any difference.

Jeff Reisman

Well, I've spent way too much time on this... Since your COUNTIFS formula works as intended, just stick it inside an IF to get your Yes/No values by checking to see if the count is greater than zero:

=IF(COUNTIFS({Vacations/Holidays - DDQC Range - weeks}, CONTAINS([36-2021]$1, @cell), {Vacations/Holidays - DDQC Range - assigned}, $[DDQC Person]@row) > 0, "Yes", "No")

ChelseaH

@Jeff Reisman

Thank you for the help! I figured I was just overlooking an incredibly simple solution. Sorry to be such a bother.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/320433#Comment_320433

No worries! I learned some stuff while working on this! I was too interested in getting your formula to work that I overlooked it too. Sometimes you just gotta stop trying to make something happen that's not going to happen 😉

Kris Custer

I was battling the same problem and came across this post hoping for a solution. I tried switching the order and if worked beautifully.

=IF(AND([Other Blocker(s)]@row = "", CONTAINS("Other", [Identified Blockers]@row)), 0,1). In stead of IF(CONTAINS(AND, I changed it to IF(AND(criteria1, range1,CONTAINS(criteria2,range2)),0,1.