IF/AND/CONTAINS Formula (2024)

Options

ChelseaH ✭✭✭✭

02/15/22 in Formulas and Functions

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:

0 · Share on FacebookShare on Twitter

Jeff Reisman ✭✭✭✭✭✭
02/22/22 Answer ✓

Options
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")

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!
0 · Share on FacebookShare on Twitter

Answers

Jeff Reisman ✭✭✭✭✭✭
02/16/22

Options
Your answer may lie in the error message.
#INVALID OPERATION
Cause
Operators (seeCreate and Edit Formulasfor 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. SeeCreate and Edit Formulasfor 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.
See Also
Using IF, FUNCTIONS (HAS, CONTAINS, etc.), and OR (for searched fields).Value if cell contains text Using COUNTIFS with CONTAINS
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

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!
0 · Share on FacebookShare on Twitter
ChelseaH ✭✭✭✭
02/17/22

Options
@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!

0 · Share on FacebookShare on Twitter
Jeff Reisman ✭✭✭✭✭✭
02/17/22

Options
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")

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!
0 · Share on FacebookShare on Twitter
ChelseaH ✭✭✭✭
02/21/22

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

0 · Share on FacebookShare on Twitter
ChelseaH ✭✭✭✭
02/21/22

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

0 · Share on FacebookShare on Twitter
Jeff Reisman ✭✭✭✭✭✭
02/21/22

Options
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:

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!
0 · Share on FacebookShare on Twitter
ChelseaH ✭✭✭✭
02/22/22

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

0 · Share on FacebookShare on Twitter
Jeff Reisman ✭✭✭✭✭✭
02/22/22 Answer ✓

Options
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")

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!
0 · Share on FacebookShare on Twitter
ChelseaH ✭✭✭✭
02/22/22

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

0 · Share on FacebookShare on Twitter
Jeff Reisman ✭✭✭✭✭✭
02/22/22

Options
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 😉

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!
0 · Share on FacebookShare on Twitter
Kris Custer ✭
04/27/22

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

0 · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("searchbox"+index); Coveo.initSearchbox( searchBoxRoot, "https://search.smartsheet.com/community/" ); }); $('#titleBar .CoveoSearchbox').hide(); $('.coveoSearchButton').on("click",function(){ $('#titleBar .CoveoSearchbox').toggle(); }); // ====== Front Page Search Box ====== $('#Form_search').remove(); var boxes = ["#search-0", "#search-1", "#search-2", "#search-3", "#search-4", "#search-5"]; console.log("PT: Before search box replacement"); $(boxes).each(function(i, val) { // ====== Front Page Search Box ====== $(val).each(function( index ) { console.log(this); $('#searchbox' + i).remove(); $(this).find('form').remove(); $(this).addClass('fpsearchbox'); $(this).attr('id', 'fpsearchbox' + index); $(this).append('

'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("fpsearchbox"+index); var action = "https://search.smartsheet.com/community/"; if ( $('body').hasClass('Categories')) { var category=$('h1.heading-1').text(); if (category === "Smartsheet Product Feedback & Ideas") { category = encodeURIComponent(category); Coveo.$(searchBoxRoot).on('buildingQuery', function(e, args) { args.queryBuilder.advancedExpression.addFieldExpression('@communitycategory', '==', [category]); }); action = action + '#&f:@communitycategory=['+category+']'; } } Coveo.initSearchbox( searchBoxRoot, action ); }); }); });

IF/AND/CONTAINS Formula (2024)

Answers

Help Article Resources

Categories

References