Search for Text in a Referenced Sheet

Options

Hi, I am trying to search for text in another sheet column that I am referencing. Error comes back as Incorrect Argument. Everything before the CONAINS function seems to work fine. I can't get the CONTAINS to work with a sheet reference.

=SUMIFS({2023-24 Estimated}, {Fund 41}, "05 Cap", {WBS}, "Facility Upgrades P-006680.01", {Approved}, 1, {Not_Appr}, 0, {Internal Order}, "Elbow-FDPFBEBO", CONTAINS("Ready Shack", {Descript_Ready_Shack}))

Answers

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    Options

    I don't have a great way to test this for you, but you may want to try adding "@cell" after {Descript_Ready_Shack} in your CONTAINS statement. So:

    CONTAINS("Ready Shack", {Descript_Ready_Shack}, @cell))

    I'm guessing that you're getting the error, because the CONTAINS statement is trying to evaluate the entire referenced column rather than looking at it cell-by-cell. The "@cell" reference may help.

  • RBerg
    RBerg ✭✭
    Options

    Hi, thanks for you response. Still getting the #Iincorrect Argument Set error.

  • Lauren Dominique
    Lauren Dominique ✭✭✭✭✭
    Options

    Similar to Danielle’s comment, I don’t have a good way to test but I wonder if you could use the HAS function instead of CONTAINS. The “HAS” is more strict than the “CONTAINS” in terms of what it’s searching for, but I sometimes have found that one works over the other (though I don’t always know the logic behind that…).

    If this comment helped you, please help me and help others by using the buttons below if you found it 💡 Insightful or ❤️ Awesome!

  • RBerg
    RBerg ✭✭
    Options

    Thanks for you comment. I cannot use the HAS function as it will not do what I need. I am looking for a certain set of characters in a longer string. HAS does not appear to work that way as I understand. So this is why I am using the CONTAING function.

    Still no luck. Just about ready to hang up the towel on this one. Should be simple enough but still getting INCORRECT ARGUMENT SET error.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    The CONTAINS portion of your formula is not in the right format. I think something like the below should work:

    =SUMIFS({2023-24 Estimated}, {Fund 41}, "05 Cap", {WBS}, "Facility Upgrades P-006680.01", {Approved}, 1, {Not_Appr}, 0, {Internal Order}, "Elbow-FDPFBEBO",{Descript_Ready_Shack}, CONTAINS("Ready Shack", @cell))

    Hope this helps!

  • RBerg
    RBerg ✭✭
    Options

    Hi, thanks. I get an #INVALID OPERATION error when I do it that way.

  • SteyJ
    SteyJ ✭✭✭✭✭✭
    edited 11/11/23
    Options
    =SUMIFS({2023-24 Estimated}, {Fund 41}, "05 Cap", 
    {WBS}, "Facility Upgrades P-006680.01", {Approved}, 1, {Not_Appr}, 0,
    {Internal Order}, "Elbow-FDPFBEBO", 
    {Descript_Ready_Shack}, CONTAINS("Ready Shack", @cell))
    

    I broke it down. This is the correct use case of the CONTAINS function when searching a column for @cell.

    If you're getting invalid operation, your formula may be correct -- but you will need to make sure none of the cells you're checking are invalid or the wrong data types.

    In this case, it seems like it may be {Approved} or {Not_Appr} has bad data inside some of the cells?, if you're using a formula for those values, make sure to use =IFERROR(Formulas here), "") to default to a useable datatype.

    Sincerely,

    Jacob Stey

  • RBerg
    RBerg ✭✭
    Options

    Thanks to all. I did get this working. I appreciate your responses as they all helped me to get this figured out.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!