Search for Text in a Referenced Sheet

RBerg

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}))

Danielle Arteaga

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

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

Lauren Dominique

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…).

RBerg

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

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

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

SteyJ

https://community.smartsheet.com/discussion/comment/404188#Comment_404188

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

RBerg

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