Formulae help....new to Smartsheet
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.
Comments
-
Paul Newcome ✭✭✭✭✭✭=SUMIFS([Column D]:[Column D], [Column A]:[Column A], 0, [Column B]:[Column B])
Give this a try.
thinkspi.com
-
J. Craig Williams ✭✭✭✭✭✭Paul's answer is incorrect.
The second criterion range needs a criterion.
=SUMIFS([Column D]:[Column D], [Column A]:[Column A], 0, [Column B]:[Column B], 0)
To be clear, this formula will sum if BOTH A and B are unticked. If EITHER are ticked, the value in D will not be added to the total.
If that is not what was asked for, please clarify.
Craig
-
Paul Newcome ✭✭✭✭✭✭
Thanks J.
I could have sworn I put that in there. Good catch.
thinkspi.com
Help Article Resources
Categories
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":"
