@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
Concatenate returning 'unparseable' - doesn't like the " (" or the ") " in the formula Hi I really hope someone can help me with this please...I'm sure there is just a tiny tweak I need to make, but I can't figure it out. I've tried this formula in a helper column but it doesn't like the " (" or the ") " in the formula. It only seems to be able to cope with + "" or + "-". This is the formula that's coming…
I want to count past due tasks using 2 date columns. 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…
Need Formula Guidance for finding matches between 2 sheets 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))…