How to SUM a formula
I have a long formula string and I need to find a sum of data within that string.
Just to give perspective, my data set is:=条件统计(食品网络:delive LaneTitle: LaneTitle。rables in progress:design:design-doing:d blocked") + COUNTIF(LaneTitle:LaneTitle, "food network:deliverables in progress:design:design-doing:design doing") + COUNTIF(LaneTitle:LaneTitle, "food network:deliverables in progress:production:sme review:sme review") + COUNTIF(LaneTitle:LaneTitle, "food network:deliverables in progress:production:prod-doing:p blocked") + COUNTIF(LaneTitle:LaneTitle, "food network:deliverables in progress:production:prod-doing:prod-doing") + COUNTIF(LaneTitle:LaneTitle, "food network:deliverables in progress:publishing/printing:publishing/printing blocked") + COUNTIF(LaneTitle:LaneTitle, "food network:deliverables in progress:production:stitching-ready:stitch blocked") + COUNTIF(LaneTitle:LaneTitle, "food network:deliverables in progress:production:translation:spn") + COUNTIF(LaneTitle:LaneTitle, "food network:deliverables in progress:publishing/printing:publishing:stitching-doing") + COUNTIF(LaneTitle:LaneTitle, "food network:deliverables in progress:design:design-ready:dr blocked") + COUNTIF(LaneTitle:LaneTitle, "food network:deliverables in progress:design:design-ready:design ready") + COUNTIF(LaneTitle:LaneTitle, "food network:deliverables in progress:production:SME Review:SME Blocked") + COUNTIF(LaneTitle:LaneTitle, "food network:deliverables in progress:production:translation:Can") + COUNTIF(LaneTitle:LaneTitle, "food network:deliverables in progress:production:translation:translation blocked")
I need to take this data and figure out:=SUMIFS([Card_Size]:[Card_Size]
How can I do this? Or do I need to SUMIF for every COUNTIF data set?
I've tried several scenarios, but I'm getting 'incorrect argument' and 'unparseable'
Help please?!
Best Answer
-
Mark Cronk ✭✭✭✭✭✭Good evening, I failed to include the @cell with each criteria. Try these:
=条件统计(LaneTitle: LaneTitle或(netw @cell = "食物ork:deliverables in progress:design:design-doing:d blocked", @cell="food network:deliverables in progress:design:design-doing:design doing", @cell= "food network:deliverables in progress:production:sme review:sme", @cell="food network:deliverables in progress:production:prod-doing:p blocked", @cell= "food network:deliverables in progress:production:prod-doing:prod-doing", @cell= "food network:deliverables in progress:publishing/printing:publishing/printing blocked", @cell= "food network:deliverables in progress:production:stitching-ready:stitch blocked", @cell= "food network:deliverables in progress:production:translation:spn", @cell="food network:deliverables in progress:publishing/printing:publishing:stitching-doing", @cell="food network:deliverables in progress:design:design-ready:dr blocked", @cell= "food network:deliverables in progress:design:design-ready:design ready", @cell= "food network:deliverables in progress:production:SME Review:SME Blocked", @cell= "food network:deliverables in progress:production:translation:Can", @cell="food network:deliverables in progress:production:translation:translation blocked"))
=SUMIFS([Card_Size]:[Card_Size], LaneTitle:LaneTitle, OR( @cell="food network:deliverables in progress:design:design-doing:d blocked", @cell="food network:deliverables in progress:design:design-doing:design doing", @cell= "food network:deliverables in progress:production:sme review:sme", @cell="food network:deliverables in progress:production:prod-doing:p blocked", @cell= "food network:deliverables in progress:production:prod-doing:prod-doing", @cell= "food network:deliverables in progress:publishing/printing:publishing/printing blocked", @cell= "food network:deliverables in progress:production:stitching-ready:stitch blocked", @cell= "food network:deliverables in progress:production:translation:spn", @cell="food network:deliverables in progress:publishing/printing:publishing:stitching-doing", @cell="food network:deliverables in progress:design:design-ready:dr blocked", @cell= "food network:deliverables in progress:design:design-ready:design ready", @cell= "food network:deliverables in progress:production:SME Review:SME Blocked", @cell= "food network:deliverables in progress:production:translation:Can", @cell="food network:deliverables in progress:production:translation:translation blocked"))
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Mark Cronk ✭✭✭✭✭✭
Hi@Brooke Clem,
Convert your formula to:
=COUNTIF(LaneTitle:LaneTitle, OR( "food network:deliverables in progress:design:design-doing:d blocked", "food network:deliverables in progress:design:design-doing:design doing", "food network:deliverables in progress:production:sme review:sme, "food network:deliverables in progress:production:prod-doing:p blocked", "food network:deliverables in progress:production:prod-doing:prod-doing", "food network:deliverables in progress:publishing/printing:publishing/printing blocked", "food network:deliverables in progress:production:stitching-ready:stitch blocked", "food network:deliverables in progress:production:translation:spn", "food network:deliverables in progress:publishing/printing:publishing:stitching-doing", "food network:deliverables in progress:design:design-ready:dr blocked", "food network:deliverables in progress:design:design-ready:design ready", "food network:deliverables in progress:production:SME Review:SME Blocked", "food network:deliverables in progress:production:translation:Can", "food network:deliverables in progress:production:translation:translation blocked"))
=SUMIFS([Card_Size]:[Card_Size], LaneTitle:LaneTitle, OR( "food network:deliverables in progress:design:design-doing:d blocked", "food network:deliverables in progress:design:design-doing:design doing", "food network:deliverables in progress:production:sme review:sme, "food network:deliverables in progress:production:prod-doing:p blocked", "food network:deliverables in progress:production:prod-doing:prod-doing", "food network:deliverables in progress:publishing/printing:publishing/printing blocked", "food network:deliverables in progress:production:stitching-ready:stitch blocked", "food network:deliverables in progress:production:translation:spn", "food network:deliverables in progress:publishing/printing:publishing:stitching-doing", "food network:deliverables in progress:design:design-ready:dr blocked", "food network:deliverables in progress:design:design-ready:design ready", "food network:deliverables in progress:production:SME Review:SME Blocked", "food network:deliverables in progress:production:translation:Can", "food network:deliverables in progress:production:translation:translation blocked"))
Work?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Brooke Clem ✭✭Not yet. I'm getting an #unparseable. But, I might need to just twiddle with it a bit more. I'll try a few things.
-
Mark Cronk ✭✭✭✭✭✭
Good evening, I failed to include the @cell with each criteria. Try these:
=条件统计(LaneTitle: LaneTitle或(netw @cell = "食物ork:deliverables in progress:design:design-doing:d blocked", @cell="food network:deliverables in progress:design:design-doing:design doing", @cell= "food network:deliverables in progress:production:sme review:sme", @cell="food network:deliverables in progress:production:prod-doing:p blocked", @cell= "food network:deliverables in progress:production:prod-doing:prod-doing", @cell= "food network:deliverables in progress:publishing/printing:publishing/printing blocked", @cell= "food network:deliverables in progress:production:stitching-ready:stitch blocked", @cell= "food network:deliverables in progress:production:translation:spn", @cell="food network:deliverables in progress:publishing/printing:publishing:stitching-doing", @cell="food network:deliverables in progress:design:design-ready:dr blocked", @cell= "food network:deliverables in progress:design:design-ready:design ready", @cell= "food network:deliverables in progress:production:SME Review:SME Blocked", @cell= "food network:deliverables in progress:production:translation:Can", @cell="food network:deliverables in progress:production:translation:translation blocked"))
=SUMIFS([Card_Size]:[Card_Size], LaneTitle:LaneTitle, OR( @cell="food network:deliverables in progress:design:design-doing:d blocked", @cell="food network:deliverables in progress:design:design-doing:design doing", @cell= "food network:deliverables in progress:production:sme review:sme", @cell="food network:deliverables in progress:production:prod-doing:p blocked", @cell= "food network:deliverables in progress:production:prod-doing:prod-doing", @cell= "food network:deliverables in progress:publishing/printing:publishing/printing blocked", @cell= "food network:deliverables in progress:production:stitching-ready:stitch blocked", @cell= "food network:deliverables in progress:production:translation:spn", @cell="food network:deliverables in progress:publishing/printing:publishing:stitching-doing", @cell="food network:deliverables in progress:design:design-ready:dr blocked", @cell= "food network:deliverables in progress:design:design-ready:design ready", @cell= "food network:deliverables in progress:production:SME Review:SME Blocked", @cell= "food network:deliverables in progress:production:translation:Can", @cell="food network:deliverables in progress:production:translation:translation blocked"))
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Brooke Clem ✭✭
This is it! Thank you so much! I've never used the @cell before. very interesting. I appreciate your help!
-
Mark Cronk ✭✭✭✭✭✭
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/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":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":22,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+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-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+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-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
