How can I compare two cells in a cross sheet reference?

Janet Skjerven
Janet Skjerven ✭✭✭
inFormulas and Functions

Hello,

I have a COUNTIFS formula (below) that returns the number of items that pass all the tests.

Basically, count an item if it's unique id is not blank, it's % complete is 100, and the risk area is Antiboycott. This formula works beautifully.

=COUNTIFS({ExportUniqueID}, NOT(ISBLANK(@cell)), {ExpComp}, @cell = 1, {ExportWPRA}, ="Antiboycott")

I want to add one more test to determine if the projected end date and the actual end date are the same, indicating that the task complete on schedule. I'm having a problem with syntax. I tried the following, and got #UNPARSEABLE.

=COUNTIFS({ExportUniqueID}, NOT(ISBLANK(@cell)), {ExpComp}, @cell = 1, {ExportWPRA}, ="Antiboycott", {ExpProjEnd}@cell = {ExpActEnd}@cell)

I'm struggling with how to format the last test. Any help is truly appreciated!

Thanks

Janet

Tags:
· Share on Facebook Share on Twitter

Best Answer

Answers

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公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Try removing one of the closing parenthesis after the DATE function.<\/p>"},{"commentID":388644,"body":"

Thanks! That sorted it!😀<\/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":[]},{"discussionID":108399,"type":"question","name":"Smartsheet Behaviours","excerpt":"I am trying to create a form that throws back a response when you tick a box like the example below. I have been told to use the logic but i cant seem to find a logic that will work, any suggestions? Additionally, if they tick an answer and it is incorrect, I want to show the text in red and if correct the text is in…","snippet":"I am trying to create a form that throws back a response when you tick a box like the example below. I have been told to use the logic but i cant seem to find a logic that will…","categoryID":322,"dateInserted":"2023-08-02T07:07:49+00:00","dateUpdated":"2023-08-02T11:36:01+00:00","dateLastComment":"2023-08-03T13:01:11+00:00","insertUserID":161048,"insertUser":{"userID":161048,"name":"Julieh","url":"https:\/\/community.smartsheet.com\/profile\/Julieh","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-02T23:38:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"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-03T13:24:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":29,"score":null,"hot":3382029540,"url":"https:\/\/community.smartsheet.com\/discussion\/108399\/smartsheet-behaviours","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108399\/smartsheet-behaviours","format":"Rich","lastPost":{"discussionID":108399,"commentID":388680,"name":"Re: Smartsheet Behaviours","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388680#Comment_388680","dateInserted":"2023-08-03T13:01:11+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-08-03T13:24:36+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\/TZJ2NJ1GP5G8\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-03T09:08:52+00:00","dateAnswered":"2023-08-02T12:54:16+00:00","acceptedAnswers":[{"commentID":388451,"body":"

You would have to insert a new header field into the form that has that particular verbiage formatted the way you want then use the form field logic to display this header row if one of the incorrect answers is selected.<\/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":108391,"type":"question","name":"Can I use groups to create series?","excerpt":"I have this report and from this I have created this Chart but I want it to drill down to the show the individual Branches on a stacked column chart like this. is this at all possible?","snippet":"I have this report and from this I have created this Chart but I want it to drill down to the show the individual Branches on a stacked column chart like this. is this at all…","categoryID":322,"dateInserted":"2023-08-02T02:31:48+00:00","dateUpdated":"2023-08-02T11:36:34+00:00","dateLastComment":"2023-08-03T13:06:28+00:00","insertUserID":163489,"insertUser":{"userID":163489,"name":"Benny C","url":"https:\/\/community.smartsheet.com\/profile\/Benny%20C","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xq_RMfil_3w!!B9mU9H6NId1","dateLastActive":"2023-08-03T00:58:31+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"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-03T13:24:36+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3382013296,"url":"https:\/\/community.smartsheet.com\/discussion\/108391\/can-i-use-groups-to-create-series","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108391\/can-i-use-groups-to-create-series","format":"Rich","lastPost":{"discussionID":108391,"commentID":388682,"name":"Re: Can I use groups to create series?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388682#Comment_388682","dateInserted":"2023-08-03T13:06:28+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-08-03T13:24:36+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\/UYHRDRG9I6TS\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-03T00:59:37+00:00","dateAnswered":"2023-08-02T12:47:07+00:00","acceptedAnswers":[{"commentID":388443,"body":"

This isn't possible in a report. You would have to use a specifically structured metrics sheet with cross sheet references pulling in your totals.<\/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":[]}],"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

  • Date formula to set cut off period

    I am trying to set a date formula to for the following statement; if the [Next Due Date] is <= 27/07/2023 return "Performed" Example: =IF(ISBLANK([Last inspection]@row), "", IF(DATE(2023,7,27)) < [Next Due Date]@row, "Overdue", "Performed"))

  • Smartsheet Behaviours

    I am trying to create a form that throws back a response when you tick a box like the example below. I have been told to use the logic but i cant seem to find a logic that will work, any suggestions? Additionally, if they tick an answer and it is incorrect, I want to show the text in red and if correct the text is in…

  • Can I use groups to create series?

    I have this report and from this I have created this Chart but I want it to drill down to the show the individual Branches on a stacked column chart like this. is this at all possible?