Formulas not adding as numbers

Hello,

I am using the formula =IF(Status2 = [HIDE: Statuses]1, "15") to add a set percentage to a completed task when the box is checked. It works, but then when I go to SUM those numbers, they simply combine the numbers (as though they're text) rather than adding them together (see %Complete1 and %Complete4). How do I make the SUM formula recognize these as numbers?

Thanks!

Laura

Tags:

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Hi @L Darvesh<\/a> <\/p>

This should work, just needed to add in a + before the [CS Lead] element<\/p>

=[Client Short Name country - Project name]@row +\" - \"+ [Event Location]@row + \" (\"+<\/strong> [CS Lead]@row + \") \"<\/p>

hope that helps <\/p>

Thanks<\/p>

Paul<\/p>"},{"commentID":401159,"body":"

Hi @L Darvesh<\/a>,<\/p>

You need a + between the opening bracket and the [CS Lead]@row:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

This is what's causing an #UNPARSEABLE error to crop up.<\/p>

Hopefully this resolves the issue!<\/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":112005,"type":"question","name":"I want to count past due tasks using 2 date columns.","excerpt":"Hello all, I'm trying to count overdue tasks by using 2 different date columns. I'm currently using this formula , =COUNTIFS([Hardware Actual Construction NTP]:[Hardware Actual Construction NTP], <[Hardware Planned Construction NTP]:[Hardware Planned Construction NTP]) However, whatever dates get put into the columns the…","snippet":"Hello all, I'm trying to count overdue tasks by using 2 different date columns. I'm currently using this formula , =COUNTIFS([Hardware Actual Construction NTP]:[Hardware Actual…","categoryID":322,"dateInserted":"2023-10-23T11:52:15+00:00","dateUpdated":null,"dateLastComment":"2023-10-23T13:37:13+00:00","insertUserID":165764,"insertUser":{"userID":165764,"name":"Cidniz","title":"Manager","url":"https:\/\/community.smartsheet.com\/profile\/Cidniz","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-23T17:32:05+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-10-23T16:23:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3396131968,"url":"https:\/\/community.smartsheet.com\/discussion\/112005\/i-want-to-count-past-due-tasks-using-2-date-columns","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112005\/i-want-to-count-past-due-tasks-using-2-date-columns","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":112005,"commentID":401135,"name":"Re: I want to count past due tasks using 2 date columns.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401135#Comment_401135","dateInserted":"2023-10-23T13:37:13+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-10-23T16:23:56+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/XQ2RPEU6PB8T\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/XQ2RPEU6PB8T\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/XQ2RPEU6PB8T\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/XQ2RPEU6PB8T\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/XQ2RPEU6PB8T\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/XQ2RPEU6PB8T\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-23T13:19:04+00:00","dateAnswered":"2023-10-23T12:21:38+00:00","acceptedAnswers":[{"commentID":401118,"body":"

Hi @Cidniz<\/a> <\/p>

The easiest way to do this is to add a Helper column doing the check for you and then use this.<\/p>

Helper column formula:<\/p>

=IF([Hardware Planned Construction NTP]@row < [Hardware Actual Construction NTP]@row, 1, 0)<\/p>

Then either:<\/p>

=SUM([Helper column]:[Helper column]) - if you left the Helper column as a number<\/p>

=COUNTIF([Helper column]:[Helper column], 1) - if you had the Helper column as a checkbox<\/p>

Hope this helps, but any questions\/problems then just ask! 🙂<\/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":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":111986,"type":"question","name":"Need Formula Guidance for finding matches between 2 sheets","excerpt":"I am trying to identify a match for a Deal ID between 2 sheets. I need to provide stats on the results. CSRSheet is Sheet 1's Deal ID Column Sheet 2, where Deal ID is, are form submissions I added a checkbox column for the match formula in Sheet 2. I was using this =IF(INDEX({CSRSheet}, MATCH([Deal ID]@row, {CSRSheet}, 0))…","snippet":"I am trying to identify a match for a Deal ID between 2 sheets. I need to provide stats on the results. CSRSheet is Sheet 1's Deal ID Column Sheet 2, where Deal ID is, are form…","categoryID":322,"dateInserted":"2023-10-20T20:53:16+00:00","dateUpdated":null,"dateLastComment":"2023-10-23T14:13:15+00:00","insertUserID":168873,"insertUser":{"userID":168873,"name":"Tammy McCauley","title":"Legal Operations","url":"https:\/\/community.smartsheet.com\/profile\/Tammy%20McCauley","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-23T14:53:26+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":168873,"lastUser":{"userID":168873,"name":"Tammy McCauley","title":"Legal Operations","url":"https:\/\/community.smartsheet.com\/profile\/Tammy%20McCauley","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-23T14:53:26+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":50,"score":null,"hot":3395907991,"url":"https:\/\/community.smartsheet.com\/discussion\/111986\/need-formula-guidance-for-finding-matches-between-2-sheets","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111986\/need-formula-guidance-for-finding-matches-between-2-sheets","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":111986,"commentID":401146,"name":"Re: Need Formula Guidance for finding matches between 2 sheets","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/401146#Comment_401146","dateInserted":"2023-10-23T14:13:15+00:00","insertUserID":168873,"insertUser":{"userID":168873,"name":"Tammy McCauley","title":"Legal Operations","url":"https:\/\/community.smartsheet.com\/profile\/Tammy%20McCauley","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-23T14:53:26+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-23T14:13:19+00:00","dateAnswered":"2023-10-23T01:13:19+00:00","acceptedAnswers":[{"commentID":401099,"body":"

@Tammy McCauley<\/a> Try this<\/p>

=IF([Deal ID]@row = \"//www.santa-greenland.com/community/discussion/44246/\", 0, IF(INDEX({CSRSheet}, MATCH([Deal ID]@row, {CSRSheet}, 0)) = [Deal ID]@row, 1, 0))<\/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"}]}],"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