IF/AND/CONTAINS Formula
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([382021]$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} = [512021]$1), AND({Vacations/Holidays  DDQC Range  assigned} = $[DDQC Person]@row, {Vacations/Holidays  DDQC Range  start week} = [512021]$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([362021]$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:
Best Answer

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([362021]$1, @cell), {Vacations/Holidays  DDQC Range  assigned}, $[DDQC Person]@row) > 0, "Yes", "No")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

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([382021]$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:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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!

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:
=IF(AND(CONTAINS([382021]$1, {Vacations/Holidays  DDQC Range  weeks}), {Vacations/Holidays  DDQC Range  assigned}, HAS(@cell, $[DDQC Person]@row)), "YES", "NO")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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.

@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...

The HAS function was specifically designed to work with multiselect 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 multiselect 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([382021]$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:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

I'm using a singleselect 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 382021 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.

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([362021]$1, @cell), {Vacations/Holidays  DDQC Range  assigned}, $[DDQC Person]@row) > 0, "Yes", "No")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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

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 😉
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 429 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!