Formulae help....new to Smartsheet

Scarfy73
Scarfy73 ✭
edited 12/09/19 inFormulas and Functions

Hi everyone,

I am a new user to smart sheet. I need to do a sumif calc based on two columns of data being 'unticked' so I want to show the sum of i.e Column D but only if Column A & B remain unticked. When a cell is ticked it means that the work has been complete etc.

Any help/advise would be greatly appreciated.

Tags:
· Share on Facebook Share on Twitter

Comments

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公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
Hi @JoannaAU<\/a> <\/p>

I hope you're well and safe!<\/p>

Have you explored using Reports or cross-sheet formulas instead?<\/p>

Would that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":107178,"type":"question","name":"IF statement that can pull data from more than one cell if","excerpt":"We are using Smartsheet as a ticketing system for reporting guest issues. I have set up a form for our team to use where they can select the \"primary issue category\", and then based on the issue they choose, it gives them another drop down where they can choose a \"secondary issue\" where they can be more specific about what…","snippet":"We are using Smartsheet as a ticketing system for reporting guest issues. I have set up a form for our team to use where they can select the \"primary issue category\", and then…","categoryID":322,"dateInserted":"2023-07-03T15:00:13+00:00","dateUpdated":null,"dateLastComment":"2023-07-04T09:51:14+00:00","insertUserID":161052,"insertUser":{"userID":161052,"name":"hannahstayo","title":"Hannah","url":"https:\/\/community.smartsheet.com\/profile\/hannahstayo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-04T15:26:02+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-04T11:23:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":54,"score":null,"hot":3376864887,"url":"https:\/\/community.smartsheet.com\/discussion\/107178\/if-statement-that-can-pull-data-from-more-than-one-cell-if","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107178\/if-statement-that-can-pull-data-from-more-than-one-cell-if","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107178,"commentID":383628,"name":"Re: IF statement that can pull data from more than one cell if","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383628#Comment_383628","dateInserted":"2023-07-04T09:51:14+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-04T11:23:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-03T16:35:33+00:00","dateAnswered":"2023-07-03T16:12:33+00:00","acceptedAnswers":[{"commentID":383537,"body":"

Try this:<\/p>

=JOIN(COLLECT([Maintenance issue - helper]@row:[Security issue - helper]@row, [Maintenance issue - helper]@row:[Security issue - helper]@row, @cell <> \"//www.santa-greenland.com/community/discussion/29536/\"), CHAR(10))<\/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":107194,"type":"question","name":"Why is my IF formula not producing the correct color circles?","excerpt":"Here is the formula I am using and every time I adjust it the formula always returns a red circle no matter what I change: =IF([Profit Margin]@row < \"30.00%\", \"Red\", IF(AND([Profit Margin]@row = \"30.00%\"), \"Yellow\", \"Green\")) I have also tried this formula: =IF([Profit Margin]@row < \"30.00%\", \"Red\", (IF(AND([Profit…","snippet":"Here is the formula I am using and every time I adjust it the formula always returns a red circle no matter what I change: =IF([Profit Margin]@row < \"30.00%\", \"Red\",…","categoryID":322,"dateInserted":"2023-07-03T21:09:14+00:00","dateUpdated":"2023-07-03T21:15:00+00:00","dateLastComment":"2023-07-03T22:33:55+00:00","insertUserID":163108,"insertUser":{"userID":163108,"name":"smbrown929","title":"Office Manager","url":"https:\/\/community.smartsheet.com\/profile\/smbrown929","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-03T23:20:25+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":163108,"lastUserID":163108,"lastUser":{"userID":163108,"name":"smbrown929","title":"Office Manager","url":"https:\/\/community.smartsheet.com\/profile\/smbrown929","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-03T23:20:25+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":32,"score":null,"hot":3376843989,"url":"https:\/\/community.smartsheet.com\/discussion\/107194\/why-is-my-if-formula-not-producing-the-correct-color-circles","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107194\/why-is-my-if-formula-not-producing-the-correct-color-circles","format":"Rich","lastPost":{"discussionID":107194,"commentID":383603,"name":"Re: Why is my IF formula not producing the correct color circles?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383603#Comment_383603","dateInserted":"2023-07-03T22:33:55+00:00","insertUserID":163108,"insertUser":{"userID":163108,"name":"smbrown929","title":"Office Manager","url":"https:\/\/community.smartsheet.com\/profile\/smbrown929","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-03T23:20:25+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-03T22:38:43+00:00","dateAnswered":"2023-07-03T22:23:39+00:00","acceptedAnswers":[{"commentID":383601,"body":"

@smbrown929<\/a>, when you add quotes around the percentage, you turn it into a word (vs a number). Try this:<\/p>

=IF([Profit Margin]@row < 0.3, \"Red\", IF(AND([Profit Margin]@row = 0.03), \"Yellow\", \"Green\"))<\/p>

Numbers don't need\/shouldn't have quotes around them.<\/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

  • Update Parent Row with Child

    I have several budget sheets that are managed by separate functions. I want the leads of each function to update their data within their own respective sheet and for the updates to flow into one collated Master budget sheet. The Master budget sheet is linked to the individual function budget sheets using a unique…

  • IF statement that can pull data from more than one cell if

    We are using Smartsheet as a ticketing system for reporting guest issues. I have set up a form for our team to use where they can select the "primary issue category", and then based on the issue they choose, it gives them another drop down where they can choose a "secondary issue" where they can be more specific about what…

  • Why is my IF formula not producing the correct color circles?

    Here is the formula I am using and every time I adjust it the formula always returns a red circle no matter what I change: =IF([Profit Margin]@row < "30.00%", "Red", IF(AND([Profit Margin]@row = "30.00%"), "Yellow", "Green")) I have also tried this formula: =IF([Profit Margin]@row < "30.00%", "Red", (IF(AND([Profit…