Formulas and Functions

1, IF(COUNTIF(Amount:Amount, Amount1) > 1, 1))) I think i am not…","categoryID":322,"dateInserted":"2020-08-26T08:08:01+00:00","dateUpdated":null,"dateLastComment":"2020-08-27T16:23:38+00:00","insertUserID":120206,"insertUser":{"userID":120206,"name":"Rahul Bhandari","url":"https:\/\/community.smartsheet.com\/profile\/Rahul%20Bhandari","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bZlVm_MBVbw!8iFWN_cX-_o!GqmssQaMSdD","dateLastActive":"2021-10-23T14:48:10+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-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":184,"score":null,"hot":4,"url":"https:\/\/community.smartsheet.com\/discussion\/70864\/duplicate-check-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70864\/duplicate-check-formula","format":"Rich","groupID":null,"statusID":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":null,"acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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}]},{"discussionID":70876,"type":"question","name":"VLOOKUP returning #INVALID REF","excerpt":"I am receiving the #INVALID REF error in smartsheet and I am not sure how to correct it. =VLOOKUP([PART NUMBER]1, [PN]1:[PART DESCRIPTION]1800, {2,3,4,5}, false) Below is the excel sheet formula that does work but does not flow over to the smartsheet. =VLOOKUP($Q6,$A4:$D25014,{2,3,4,5},FALSE) If you look at the excel…","categoryID":322,"dateInserted":"2020-08-26T16:19:31+00:00","dateUpdated":null,"dateLastComment":"2020-08-27T16:08:09+00:00","insertUserID":122850,"insertUser":{"userID":122850,"name":"Camie Keuck","url":"https:\/\/community.smartsheet.com\/profile\/Camie%20Keuck","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-25T12:54:58+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-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":14,"countViews":443,"score":null,"hot":14,"url":"https:\/\/community.smartsheet.com\/discussion\/70876\/vlookup-returning-invalid-ref","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70876\/vlookup-returning-invalid-ref","format":"Rich","groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/JUHD7QEN0087\/smartsheet.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"SMARTSHEET.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2020-08-26T20:21:33+00:00","dateAnswered":"2020-08-26T19:58:43+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":445,"urlcode":"importing-and-exporting","name":"Importing and Exporting"}],"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}]},{"discussionID":70831,"type":"question","name":"Combining OR Function with INDEX MATCH.","excerpt":"Hi, I tried using Combination of OR Function with INDEX MATCH and phrased it as below =INDEX({Ref Column}, MATCH(OR([Column #]@row, {Column # SheetA}, {Column # SheetB},{ Column # SheetC}), 0)) but it if throwing #INVALID DATA TYPE. whereas =INDEX({Ref Column}, MATCH([[Column #]@row, { Column # SheetA}, 0)) is working…","categoryID":322,"dateInserted":"2020-08-25T05:24:17+00:00","dateUpdated":null,"dateLastComment":"2020-08-27T16:00:07+00:00","insertUserID":122925,"insertUser":{"userID":122925,"name":"Mindfull","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mindfull","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-02-02T11:26:13+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-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":170,"score":null,"hot":6,"url":"https:\/\/community.smartsheet.com\/discussion\/70831\/combining-or-function-with-index-match","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70831\/combining-or-function-with-index-match","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-09-24T13:52:25+00:00","dateAnswered":"2020-08-27T16:00:07+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[],"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}]},{"discussionID":70890,"type":"question","name":"Formula - Adding together certain assigned point values","excerpt":"Hello, I have created the below formula that works up until the bolded part. I am trying to add this to the below formula: If Less than 100 it gets zero points. If between 100 & 499.9 it gets 1 point If between 500 & 999.9 it gets 2 points If greater than 999.9 (or 1,000 or more) it gets 3 points. I am not sure how to add…","categoryID":322,"dateInserted":"2020-08-26T20:07:25+00:00","dateUpdated":null,"dateLastComment":"2020-08-27T15:51:55+00:00","insertUserID":117663,"insertUser":{"userID":117663,"name":"Maria Watters","url":"https:\/\/community.smartsheet.com\/profile\/Maria%20Watters","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AGNmyxZu-WWnlgGduZfI_LUvXlkG2CG4LmGb5egg0OWksQ=s96-c","dateLastActive":"2023-05-01T20:30:47+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-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":83,"score":null,"hot":3,"url":"https:\/\/community.smartsheet.com\/discussion\/70890\/formula-adding-together-certain-assigned-point-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/70890\/formula-adding-together-certain-assigned-point-values","format":"Rich","groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2020-08-26T21:40:03+00:00","dateAnswered":"2020-08-26T20:20:55+00:00","acceptedAnswers":[]}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}],"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}]}],"title":null,"subtitle":null,"description":null,"viewAllUrl":null,"isMainContent":true,"noCheckboxes":false}">

Help Article Resources