I am trying to use SUM to add the values from multiple cells in that were populated by an IF formula

Jhinkle
Jhinkle ✭
inFormulas and Functions

I am trying to use SUM to add the values from multiple cells in that were populated by an IF formula.

Example of 2 IF formulas: =IF([Member 2 Registration Type]@row = "Golf & Dinner - General Public", "135") and =IF([Member 2 Registration Type]@row = "Dinner Only", "60")

The IF formulas are working as they should and are populating the cell.

In another cell, I am trying to sum the 135 and 60 that were generated from the IF formula, but I do not believe it is recognizing the "135" and "60" as numerical values.

Is there any ways to fix this?

· Share on Facebook Share on Twitter

Best Answer

一个nswers

  • Ro W
    Ro W ✭✭
    edited 06/12/23

    I had the same issue, all I had to do was remove the " " quotation marks around the numbers (see below) that way Smartsheet will treat the value as a number instead of a text allowing you to sum the column correctly.

    =IF([Member 2 Registration Type]@row = "Golf & Dinner - General Public", 135) and =IF([Member 2 Registration Type]@row = "Dinner Only", 60)

    · Share on Facebook Share on Twitter
  • Jhinkle
    Jhinkle ✭
    一个nswer ✓

    Thanks! That worked!

    · Share on Facebook Share on Twitter

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

 了解更多
Try this:<\/p>

=IF([Employer\/Student Agreement Received Date]@row = \"//www.santa-greenland.com/community/discussion/106342/\", IF([ESA Sent Date]@row< TODAY(-30), \"Red\", IF([ESA Sent Date]@row<TODAY(-15), \"Yellow\", \"Green\")), \"Green\")<\/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":107678,"type":"question","name":"Automatic Formula in Add Row Workflow","excerpt":"Hello. I have Sheet A which copies the rows to Sheet B once rows in Sheets A have been approved. Sheet A includes a country column and Sheet B includes both a country and region. I would like the region column to automatically generate the region value based on the country value. I have a separate region and country sheet…","snippet":"Hello. I have Sheet A which copies the rows to Sheet B once rows in Sheets A have been approved. Sheet A includes a country column and Sheet B includes both a country and region.…","categoryID":322,"dateInserted":"2023-07-17T16:45:50+00:00","dateUpdated":null,"dateLastComment":"2023-07-17T17:03:01+00:00","insertUserID":163135,"insertUser":{"userID":163135,"name":"JoannaAU","url":"https:\/\/community.smartsheet.com\/profile\/JoannaAU","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-17T17:03:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":116407,"lastUser":{"userID":116407,"name":"Darren Mullen","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Darren%20Mullen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/DKTEWPYCU2OY\/nBJQNCZXABT0R.png","dateLastActive":"2023-07-17T18:33:45+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":18,"score":null,"hot":3379227531,"url":"https:\/\/community.smartsheet.com\/discussion\/107678\/automatic-formula-in-add-row-workflow","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107678\/automatic-formula-in-add-row-workflow","format":"Rich","lastPost":{"discussionID":107678,"commentID":385593,"name":"Re: Automatic Formula in Add Row Workflow","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385593#Comment_385593","dateInserted":"2023-07-17T17:03:01+00:00","insertUserID":116407,"insertUser":{"userID":116407,"name":"Darren Mullen","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Darren%20Mullen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/DKTEWPYCU2OY\/nBJQNCZXABT0R.png","dateLastActive":"2023-07-17T18:33:45+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-07-17T16:55:04+00:00","dateAnswered":"2023-07-17T16:50:20+00:00","acceptedAnswers":[{"commentID":385587,"body":"

@JoannaAU<\/a> if you create a column formula for the region column in the destination sheet with the lookup functions, it will automatically pull on the matching value when a new row is added.<\/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":107668,"type":"question","name":"How to find if someone is assigned to two tasks on the same day?","excerpt":"Good morning, I have a sheet with due date in one column and team member assigned in another column. I want to ensure no one is assigned two tasks on the same day. I have exhausted my googling abilities and have come up empty. Any ideas here?","snippet":"Good morning, I have a sheet with due date in one column and team member assigned in another column. I want to ensure no one is assigned two tasks on the same day. I have…","categoryID":322,"dateInserted":"2023-07-17T15:23:13+00:00","dateUpdated":null,"dateLastComment":"2023-07-17T16:01:55+00:00","insertUserID":163551,"insertUser":{"userID":163551,"name":"FBowman","title":"Business Operations and Underwriting Assistant Manager","url":"https:\/\/community.smartsheet.com\/profile\/FBowman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-17T15:36:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-17T19:19:18+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":20,"score":null,"hot":3379218908,"url":"https:\/\/community.smartsheet.com\/discussion\/107668\/how-to-find-if-someone-is-assigned-to-two-tasks-on-the-same-day","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107668\/how-to-find-if-someone-is-assigned-to-two-tasks-on-the-same-day","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107668,"commentID":385584,"name":"Re: How to find if someone is assigned to two tasks on the same day?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/385584#Comment_385584","dateInserted":"2023-07-17T16:01:55+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-17T19:19:18+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-07-17T15:36:23+00:00","dateAnswered":"2023-07-17T15:30:36+00:00","acceptedAnswers":[{"commentID":385572,"body":"

You are basically looking for duplicates. Try something like this in a flag type column:<\/p>

=IF(COUNTIFS([Assigned To]:[Assigned To], @cell = [Assigned To]@row, [Due Date]:[Due Date], @cell = [Due Date]@row)> 1, 1)<\/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

  • Formula to set red/yellow/green status based on information in two fields.

    I am trying to set a field to red if an agreement has been sent 30 or more days ago but the received date is blank. I used the following two formulas, but both times, I get the unpassable error. Can you help me figure out what I'm doing wrong? =IF(AND([ESA Sent Date]@row+30(),[Employer/Student Agreement Received Date]@row…

  • 一个utomatic Formula in Add Row Workflow

    Hello. I have Sheet A which copies the rows to Sheet B once rows in Sheets A have been approved. Sheet A includes a country column and Sheet B includes both a country and region. I would like the region column to automatically generate the region value based on the country value. I have a separate region and country sheet…

  • How to find if someone is assigned to two tasks on the same day?

    Good morning, I have a sheet with due date in one column and team member assigned in another column. I want to ensure no one is assigned two tasks on the same day. I have exhausted my googling abilities and have come up empty. Any ideas here?