SUMIFS and Contains conditions

Amy Martin
Amy Martin ✭
inFormulas and Functions

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?

Tags:
· Share on Facebook Share on Twitter

Best Answer

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Hi Terri,<\/p>

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

@Katherine Britt<\/a> <\/p>

Your Match function is looking up the text 800014723 and trying to find a match, but in your source data it is stored as a number.<\/p>

Add the VALUE function to your Manager ID column, and that will convert it to a number.<\/p>

=VALUE(MID(RIGHT([Manager Name]@row, 11), 2, 9))<\/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":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":111883,"type":"question","name":"How can I create a COUNTSIF within a specified time period?","excerpt":"the formula I currently have is: =COUNTIFS([Project Status]:[Project Status], CONTAINS(\"Started\", @cell), [Area\/Region]:[Area\/Region], =\"Canada Johnston\") Now I need to have it pull information for the contract year so between say April 1, 2023 to March 31, 2024. I can not seem to be able to add the correct information.…","snippet":"the formula I currently have is: =COUNTIFS([Project Status]:[Project Status], CONTAINS(\"Started\", @cell), [Area\/Region]:[Area\/Region], =\"Canada Johnston\") Now I need to have it…","categoryID":322,"dateInserted":"2023-10-18T22:27:35+00:00","dateUpdated":null,"dateLastComment":"2023-10-19T17:03:38+00:00","insertUserID":168751,"insertUser":{"userID":168751,"name":"ShawnaK","url":"https:\/\/community.smartsheet.com\/profile\/ShawnaK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!g3DRnwzsuvU!pfjQqcjisNc!tJICt-nTV2M","dateLastActive":"2023-10-19T17:03:16+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":168751,"lastUser":{"userID":168751,"name":"ShawnaK","url":"https:\/\/community.smartsheet.com\/profile\/ShawnaK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!g3DRnwzsuvU!pfjQqcjisNc!tJICt-nTV2M","dateLastActive":"2023-10-19T17:03:16+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":20,"score":null,"hot":3395404873,"url":"https:\/\/community.smartsheet.com\/discussion\/111883\/how-can-i-create-a-countsif-within-a-specified-time-period","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111883\/how-can-i-create-a-countsif-within-a-specified-time-period","format":"Rich","lastPost":{"discussionID":111883,"commentID":400799,"name":"Re: How can I create a COUNTSIF within a specified time period?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400799#Comment_400799","dateInserted":"2023-10-19T17:03:38+00:00","insertUserID":168751,"insertUser":{"userID":168751,"name":"ShawnaK","url":"https:\/\/community.smartsheet.com\/profile\/ShawnaK","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!g3DRnwzsuvU!pfjQqcjisNc!tJICt-nTV2M","dateLastActive":"2023-10-19T17:03:16+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-19T16:09:59+00:00","dateAnswered":"2023-10-19T08:35:59+00:00","acceptedAnswers":[{"commentID":400715,"body":"

Hi @ShawnaK<\/a>,<\/p>

You would do something like this (using a column header of \"Contract Date\" in this example):<\/p>

=COUNTIFS([Project Status]:[Project Status], CONTAINS(\"Started\", @cell), [Area\/Region]:[Area\/Region], =\"Canada Johnston\",[Contract Date]:[Contract Date],AND(@cell>=DATE(2023,4,1),@cell<=DATE(2024,3,31))<\/p>

If you needed to change the dates, then you would just adjust the numbers in the DATE formulas (first number is year, then month, then day).<\/p>

Hope this helps, if there are any problems\/questions then just post! 🙂<\/span><\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Unparseable Error: Check "Checkbox" when conditions are met

    Hello. I have a helper checkbox column. I would like a formula to "check the box" when the content in the column "Frequency L4" is not blank. In addition, I would like any ancestor row of that "not blank" row marked with the check box as well. So far I have only worked on the first part and get errors. Any ideas on how to…

  • Index Match Error

    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…

  • How can I create a COUNTSIF within a specified time period?

    the formula I currently have is: =COUNTIFS([Project Status]:[Project Status], CONTAINS("Started", @cell), [Area/Region]:[Area/Region], ="Canada Johnston") Now I need to have it pull information for the contract year so between say April 1, 2023 to March 31, 2024. I can not seem to be able to add the correct information.…