SUMIFS and Contains conditions
Hi all- I have a formula that refers to another sheet to sum costs based on some criteria:
=SUMIFS({Professional Services Project Costs Amount}, {Professional Services Project Costs Grant Code}, $[Budget Allocated]$8, {Professional Services Project Costs Category}, CONTAINS([Budget Category]28, @cell))
I'd like to add another condition, which is to find the name "Denise" in a text column where we put notes, so I tried the following, but got INCORRECT ARGUMENT:
=SUMIFS({Professional Services Project Costs Amount}, {Professional Services Project Costs Grant Code}, $[Budget Allocated]$8, {Professional Services Project Costs Category}, CONTAINS([Budget Category]28, @cell), {Goods & Services Project Costs Notes}, CONTAINS("Denise"))
Any Tips?
Best Answer
-
Paul Newcome ✭✭✭✭✭✭It should be working. Hmm...
Log out
Clear your cookies and cache
Log back in
See if that clears things up for you.
Answers
-
Paul Newcome ✭✭✭✭✭✭
You just need an @cell reference is all...
=SUMIFS({Professional Services Project Costs Amount}, {Professional Services Project Costs Grant Code}, $[Budget Allocated]$8, {Professional Services Project Costs Category}, CONTAINS([Budget Category]28, @cell), {Goods & Services Project Costs Notes}, CONTAINS("Denise", @cell))
-
Paul, Unfortunately I am still getting an Incorrect Argument message with the @cell....
(new to this community, do I need to @ you when I respond?@Paul Newcome)
-
Paul Newcome ✭✭✭✭✭✭
That's odd. The syntax is otherwise correct. Can you provide a screenshot of the formula in the sheet similar to the screenshot here?
-
Maybe this makes a difference- i see the error code is "Incorrect Argument Set"
-
Paul Newcome ✭✭✭✭✭✭
The @cell should be inside of the CONTAINS function like I have it in the formula above.
CONTAINS("Denise", @cell))
-
Oof, still no-
-
Paul Newcome ✭✭✭✭✭✭
-
Yes, INCORRECT ARGUMENT SET
It doesn't like the addition of the last condition...
-
Paul Newcome ✭✭✭✭✭✭
It should be working. Hmm...
Log out
Clear your cookies and cache
Log back in
See if that clears things up for you.
-
Wow, yes, went into another browser, changed the formula, and it worked.
Thanks for your time!
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
The #UNPARSABLE error<\/a> is thrown when \"the formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.\"<\/p> ISBLANK<\/a> is a formula of it's own. It is expecting an input value between parenthesis. Here is an updated version of your formula<\/p> =IF(NOT(ISBLANK([Frequency L4]@row)), 1, 0)<\/p> Here is a help article<\/a> which should help with your other question regarding parent\/children rows.<\/p> Hope this helps!<\/p> Sal<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":111902,"type":"question","name":"Index Match Error","excerpt":"Are you able to help me with a formula problem? I am using a combination of MID and Right functions to pull a number from a text string.... Manager Name = Karri (800014723) Manager ID =MID(RIGHT([Manager Name]@row, 11), 2, 9) Manager ID = 800014723 -------------------------------- Now I am trying to use the Manager ID cell…","snippet":"Are you able to help me with a formula problem? I am using a combination of MID and Right functions to pull a number from a text string.... Manager Name = Karri (800014723)…","categoryID":322,"dateInserted":"2023-10-19T13:29:37+00:00","dateUpdated":null,"dateLastComment":"2023-10-19T14:37:01+00:00","insertUserID":124033,"insertUser":{"userID":124033,"name":"Katherine Britt","url":"https:\/\/community.smartsheet.com\/profile\/Katherine%20Britt","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AEdFTp5xSsXoObTLWpny9Ayq7VegQuwUIo98QzCkAhjW6A=s96-c","dateLastActive":"2023-10-19T14:35:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":124033,"lastUser":{"userID":124033,"name":"Katherine Britt","url":"https:\/\/community.smartsheet.com\/profile\/Katherine%20Britt","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AEdFTp5xSsXoObTLWpny9Ayq7VegQuwUIo98QzCkAhjW6A=s96-c","dateLastActive":"2023-10-19T14:35:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":20,"score":null,"hot":3395449598,"url":"https:\/\/community.smartsheet.com\/discussion\/111902\/index-match-error","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111902\/index-match-error","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":111902,"commentID":400761,"name":"Re: Index Match Error","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400761#Comment_400761","dateInserted":"2023-10-19T14:37:01+00:00","insertUserID":124033,"insertUser":{"userID":124033,"name":"Katherine Britt","url":"https:\/\/community.smartsheet.com\/profile\/Katherine%20Britt","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AEdFTp5xSsXoObTLWpny9Ayq7VegQuwUIo98QzCkAhjW6A=s96-c","dateLastActive":"2023-10-19T14:35:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-19T14:35:49+00:00","dateAnswered":"2023-10-19T14:03:50+00:00","acceptedAnswers":[{"commentID":400753,"body":"
