How do I create and If Formula pulling from another sheet to designate indicators?
Answers
-
Paul Newcome ✭✭✭✭✭✭You are missing the very first IF( at the beginning, all of your COUNTIF functions should be COUNTIFS functions, and you only need to enter the {Range}. If you click on the column header, the {Range} will automatically cover the entire column. You do not need to use {Range}:{Range}.
You are actually very very close. You've got this.
=IF(COUNTIFS(..................), "Red", IF(COUNTIFS(................), "Yellow", "Green"))
{Due Date}
:{Due Date} -
delaurellc ✭✭
Hi Paul,
Now I am getting invalid operation error :o(
=如果(条件统计({无线电日志范围2},{标准大多数我mpacted}, @cell= "Infrastructure", {Due Date}, @cell<= TODAY(), {Done}, @cell= 1> 0), "Red", IF(COUNTIFS({RADIO Log Range 2}, {Criteria Most Impacted}, @cell= "Infrastructure", {Due Date},@cell>= TODAY(2), "Yellow", "Green"))
I have tried multiple iterations of this and no joy. Am I using the correct brackets? In all the code I see for smartsheets the brackets are [] not {}
Caroline
-
Genevieve P. Employee AdminIt looks like you are missing some closing parentheses, which is what's giving you the current error.
澄清一下,这些}{交叉表再保险ferences(see here)whereas column references [in these] are looking at columns in the same sheet(see here).
Try this structure:
=如果(条件统计({无线电日志范围2},{标准大多数我mpacted}, @cell= "Infrastructure", {Due Date}, @cell<= TODAY(), {Done}, @cell=1) > 0,"Red", IF(COUNTIFS({RADIO Log Range 2}, {Criteria Most Impacted}, @cell= "Infrastructure", {Due Date},@cell>= TODAY(2)) > 0,"Yellow", "Green"))
Cheers,
Genevieve
-
Paul Newcome ✭✭✭✭✭✭
You will also need to get rid of {RADIO Log Range 2} from both COUNTIFS.
You have
range / range / criteria / range / criteria
it should be
range / criteria / range / criteria
Side note: I don't see the checkbox range/criteria set included in the second COUNTIFS.
-
Genevieve P. Employee Admin
Good catch@Paul Newcome
-
delaurellc ✭✭
Paul and Genevieve,
so I am not sure what other range I have referenced? The Criteria not Met is a column on the RADIO log Sheet so what other reference do I have to show the formula where the data to Count is?
I have removed the reference to the RADIO log and now getting #INVALID REF. This is what I have:
=IF(COUNTIFS({Criteria Most Impacted}, @cell= "Infrastructure", {Due Date}, @cell<= TODAY(), {Done}, @cell=1) > 0,"Red", IF(COUNTIFS({Criteria Most Impacted}, @cell= "Infrastructure", {Due Date},@cell>= TODAY(2)) > 0,"Yellow", "Green"))
Genevieve thank you for sending all the material, I already have the formula item downloaded and reading the other item I meet all the criteria to reference another sheet. Just goes to show you can be a data engineer on a database and something simple like excel or smartsheet can trip you up. I am feeling really frustrated at this point that I cannot figure this out.
You guys are great, I am just missing some brain cells lately.
Caroline
-
Genevieve P. Employee Admin
Hey@delaurellc
No worries - it happens to all of us and formulas can be tricky!
It sounds like each of your {references} may not have been created correctly, going to the right column in the other sheet.
Here's a recent threadwhere we went through how to create a {cross sheet reference}
All of your references will need to be created in this way:
{Criteria Most Impacted}`
{Due Date}
{Done}
Does that help?
Cheers,
Genevieve
-
delaurellc ✭✭
I am now getting an indicator but it is the wrong one :o( It is giving me the green but my the record I have in the Radio log the indicator should be showing as red because it is in the past and not done.
=IF(COUNTIFS({Criteria Most Impacted}, @cell = "Infrastructure", {Due Date}, @cell <= TODAY(), {Done}, @cell = 1) > 0, "Red", IF(COUNTIFS({Criteria Most Impacted}, @cell = "Infrastructure", {Due Date}, @cell >= TODAY(2), {Done}, @cell = 1) > 0, "Yellow", "Green"))
here is the record I am looking at right now the criteria most wanted is correct the Due date is less than today and the done cell is empty so it should show as red
It is green
something I missed? Should I try the HAS function in the last thread? and if I do HAS(@cell where does the close ) go?
-
Paul Newcome ✭✭✭✭✭✭
{Done}, @cell=1
is looking for rows where the box IS checked. We want to look for rows where the box IS NOT checked.
{Done}, @cell<>1
-
delaurellc ✭✭
This worked, thank you for hanging in there with me. I appreciate you so much!
Caroline
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
Check out theFormula Handbook template!
In your sheet, create a third section that adds the results of the first two. It would have the same rows, but the formula for each row would add the cells of section 1 + section 2.<\/p>
Add a helper column called ShowInReport as a Check Box and check all the rows in the third section. <\/p>
Create a report and tell it to show you only Section 3 (where the ShowInReport is true.<\/p>
I hope that makes sense.<\/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":108864,"type":"question","name":"Is there a formula to show as %","excerpt":"What I am trying to do is have my overview brought in automatically each month so it doesn't have to manually be typed in. The struggle I'm having is it wants to bring in as a decimal instead of a %. Below is the formula I am currently using. Is there a way to make it show as % =\"We are at \" + [% closed rate]@row + \"…","snippet":"What I am trying to do is have my overview brought in automatically each month so it doesn't have to manually be typed in. The struggle I'm having is it wants to bring in as a…","categoryID":322,"dateInserted":"2023-08-11T17:22:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T19:29:15+00:00","insertUserID":144360,"insertUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T20:24:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":144360,"lastUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T20:24:30+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":36,"score":null,"hot":3383558496,"url":"https:\/\/community.smartsheet.com\/discussion\/108864\/is-there-a-formula-to-show-as","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108864\/is-there-a-formula-to-show-as","format":"Rich","lastPost":{"discussionID":108864,"commentID":390301,"name":"Re: Is there a formula to show as %","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390301#Comment_390301","dateInserted":"2023-08-11T19:29:15+00:00","insertUserID":144360,"insertUser":{"userID":144360,"name":"Hollie Green","url":"https:\/\/community.smartsheet.com\/profile\/Hollie%20Green","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T20:24:30+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-08-11T19:29:56+00:00","dateAnswered":"2023-08-11T17:49:58+00:00","acceptedAnswers":[{"commentID":390269,"body":"
Try this - =\"We are at \" + [% closed rate]@row * 100 + \"% closed rate on ticket status for the month of \"+[Month]@row.<\/p>"},{"commentID":390301,"body":"
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/106279/how-do-i-create-and-if-formula-pulling-from-another-sheet-to-designate-indicators/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/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":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":59,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+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-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"
![https:\/\/us.v-cdn.net\/6031209\/uploads\/3W06MG6YUJQX\/screenshot-2023-06-12-at-14-34-52.png<\/a><\/p>At this point, a pop-up helper window should appear. From here, you can select an option that says "Reference Another Sheet"<\/p>Click on this, and a pop-up window will let you choose which<\/em> sheet you want to reference. Find that sheet, then find the column that has the multi-select values. <\/p>Click on the column name<\/strong> and it will select the entire column as a cross-sheet reference range<\/em>. <\/p>https:\/\/us.v-cdn.net\/6031209\/uploads\/GCDDWIP57AKS\/screenshot-2023-06-12-at-14-39-04.png<\/a><\/p>You can change the name<\/strong> of this range in the little window at the top if you want, but you don't have to. Once you have the column highlighted, click Insert Reference<\/strong>. That then creates a reference to this other sheet and that specific column!<\/p>The rest you then build out in your current sheet:<\/p>https:\/\/us.v-cdn.net\/6031209\/uploads\/XII926DPC1UT\/screenshot-2023-06-12-at-14-40-15.png<\/a><\/p>=COUNTIFS({your range}, < comma<\/p>Then HAS(@cell, <\/strong>to say look in each cell<\/em> of the previously identified range<\/p>And finally, what are you looking for? You're looking for the single value you typed in the cell to the left, the "Dropdown Column" cell. <\/p>=COUNTIFS({your range}, HAS(@cell, [Dropdown Column]@row))<\/p>The great thing about this is once you have it set up, you can right-click on the cell and turn it into a Column Formula<\/strong> <\/a>so it automatically fills the column! <\/p>Here are some references you may find useful while building this out:<\/p>Cross-sheet formulas<\/a><\/li>Webinar: Formula Foundations<\/a><\/li>Webinar: Essential Formulas for Smartsheet Users<\/a><\/li>Create and Edit Formulas in Smartsheet<\/a><\/li><\/ul>Cheers,<\/p>Genevieve<\/p>","bodyRaw":"[{\"insert\":\"Hey \"},{\"insert\":{\"mention\":{\"name\":\"KatieDi\",\"userID\":156658}}},{\"insert\":\" \\nI'd be happy to jump in here and help clarify - it sounds like you're most of the way there!\\nIn your sheet that has each individual option listed, you'll want to start building your formula by typing:\\n=COUNTIFS(\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/3W06MG6YUJQX\\\/screenshot-2023-06-12-at-14-34-52.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/3W06MG6YUJQX\\\/screenshot-2023-06-12-at-14-34-52.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\nAt this point, a pop-up helper window should appear. From here, you can select an option that says \\\"Reference Another Sheet\\\"\\n\\n\\nClick on this, and a pop-up window will let you choose \"},{\"attributes\":{\"italic\":true},\"insert\":\"which\"},{\"insert\":\" sheet you want to reference. Find that sheet, then find the column that has the multi-select values. \\nClick on the \"},{\"attributes\":{\"bold\":true},\"insert\":\"column name\"},{\"insert\":\" and it will select the entire column as a \"},{\"attributes\":{\"italic\":true},\"insert\":\"cross-sheet reference range\"},{\"insert\":\". \\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/GCDDWIP57AKS\\\/screenshot-2023-06-12-at-14-39-04.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/GCDDWIP57AKS\\\/screenshot-2023-06-12-at-14-39-04.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"You can change the \"},{\"attributes\":{\"bold\":true},\"insert\":\"name\"},{\"insert\":\" of this range in the little window at the top if you want, but you don't have to. Once you have the column highlighted, click \"},{\"attributes\":{\"bold\":true},\"insert\":\"Insert Reference\"},{\"insert\":\". That then creates a reference to this other sheet and that specific column!\\nThe rest you then build out in your current sheet:\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/XII926DPC1UT\\\/screenshot-2023-06-12-at-14-40-15.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/XII926DPC1UT\\\/screenshot-2023-06-12-at-14-40-15.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n=COUNTIFS({your range}, < comma\\nThen \"},{\"attributes\":{\"bold\":true},\"insert\":\"HAS(@cell, \"},{\"insert\":\"to say look in \"},{\"attributes\":{\"italic\":true},\"insert\":\"each cell\"},{\"insert\":\" of the previously identified range\\nAnd finally, what are you looking for? You're looking for the single value you typed in the cell to the left, the \\\"Dropdown Column\\\" cell. \\n=COUNTIFS({your range}, HAS(@cell, [Dropdown Column]@row))\\n\\nThe great thing about this is once you have it set up, you can right-click on the cell and \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2481944-set-formulas-for-all-rows-with-column-formulas\"},\"insert\":\"turn it into a \"},{\"attributes\":{\"bold\":true,\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2481944-set-formulas-for-all-rows-with-column-formulas\"},\"insert\":\"Column Formula\"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2481944-set-formulas-for-all-rows-with-column-formulas\"},\"insert\":\" \"},{\"insert\":\"so it automatically fills the column! \\n\\nHere are some references you may find useful while building this out:\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/learning-track\\\/level-3-advanced-users\\\/cross-sheet-formulas\"},\"insert\":\"Cross-sheet formulas\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/learning-track\\\/best-practice-webinars\\\/formula-foundations\"},\"insert\":\"Webinar: Formula Foundations\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Webinar: \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/learning-track\\\/best-practice-webinars\\\/essential-formulas-smartsheet-users\"},\"insert\":\"Essential Formulas for Smartsheet Users\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2476171-create-and-edit-formulas-in-smartsheet\"},\"insert\":\"Create and Edit Formulas in Smartsheet\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"\\nCheers,\\nGenevieve \\n\"}]","format":"rich","dateInserted":"2023-06-12T13:44:04+00:00","insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-11T17:39:53+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380076#Comment_380076","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/380076#Comment_380076](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/3W06MG6YUJQX\/screenshot-2023-06-12-at-14-34-52.png\"){kind=link}
![https:\/\/us.v-cdn.net\/6031209\/uploads\/GCDDWIP57AKS\/screenshot-2023-06-12-at-14-39-04.png<\/a><\/p>You can change the name<\/strong> of this range in the little window at the top if you want, but you don't have to. Once you have the column highlighted, click Insert Reference<\/strong>. That then creates a reference to this other sheet and that specific column!<\/p>The rest you then build out in your current sheet:<\/p>https:\/\/us.v-cdn.net\/6031209\/uploads\/XII926DPC1UT\/screenshot-2023-06-12-at-14-40-15.png<\/a><\/p>=COUNTIFS({your range}, < comma<\/p>Then HAS(@cell, <\/strong>to say look in each cell<\/em> of the previously identified range<\/p>And finally, what are you looking for? You're looking for the single value you typed in the cell to the left, the "Dropdown Column" cell. <\/p>=COUNTIFS({your range}, HAS(@cell, [Dropdown Column]@row))<\/p>The great thing about this is once you have it set up, you can right-click on the cell and turn it into a Column Formula<\/strong> <\/a>so it automatically fills the column! <\/p>Here are some references you may find useful while building this out:<\/p>Cross-sheet formulas<\/a><\/li>Webinar: Formula Foundations<\/a><\/li>Webinar: Essential Formulas for Smartsheet Users<\/a><\/li>Create and Edit Formulas in Smartsheet<\/a><\/li><\/ul>Cheers,<\/p>Genevieve<\/p>","bodyRaw":"[{\"insert\":\"Hey \"},{\"insert\":{\"mention\":{\"name\":\"KatieDi\",\"userID\":156658}}},{\"insert\":\" \\nI'd be happy to jump in here and help clarify - it sounds like you're most of the way there!\\nIn your sheet that has each individual option listed, you'll want to start building your formula by typing:\\n=COUNTIFS(\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/3W06MG6YUJQX\\\/screenshot-2023-06-12-at-14-34-52.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/3W06MG6YUJQX\\\/screenshot-2023-06-12-at-14-34-52.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\nAt this point, a pop-up helper window should appear. From here, you can select an option that says \\\"Reference Another Sheet\\\"\\n\\n\\nClick on this, and a pop-up window will let you choose \"},{\"attributes\":{\"italic\":true},\"insert\":\"which\"},{\"insert\":\" sheet you want to reference. Find that sheet, then find the column that has the multi-select values. \\nClick on the \"},{\"attributes\":{\"bold\":true},\"insert\":\"column name\"},{\"insert\":\" and it will select the entire column as a \"},{\"attributes\":{\"italic\":true},\"insert\":\"cross-sheet reference range\"},{\"insert\":\". \\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/GCDDWIP57AKS\\\/screenshot-2023-06-12-at-14-39-04.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/GCDDWIP57AKS\\\/screenshot-2023-06-12-at-14-39-04.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"You can change the \"},{\"attributes\":{\"bold\":true},\"insert\":\"name\"},{\"insert\":\" of this range in the little window at the top if you want, but you don't have to. Once you have the column highlighted, click \"},{\"attributes\":{\"bold\":true},\"insert\":\"Insert Reference\"},{\"insert\":\". That then creates a reference to this other sheet and that specific column!\\nThe rest you then build out in your current sheet:\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/XII926DPC1UT\\\/screenshot-2023-06-12-at-14-40-15.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/XII926DPC1UT\\\/screenshot-2023-06-12-at-14-40-15.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n=COUNTIFS({your range}, < comma\\nThen \"},{\"attributes\":{\"bold\":true},\"insert\":\"HAS(@cell, \"},{\"insert\":\"to say look in \"},{\"attributes\":{\"italic\":true},\"insert\":\"each cell\"},{\"insert\":\" of the previously identified range\\nAnd finally, what are you looking for? You're looking for the single value you typed in the cell to the left, the \\\"Dropdown Column\\\" cell. \\n=COUNTIFS({your range}, HAS(@cell, [Dropdown Column]@row))\\n\\nThe great thing about this is once you have it set up, you can right-click on the cell and \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2481944-set-formulas-for-all-rows-with-column-formulas\"},\"insert\":\"turn it into a \"},{\"attributes\":{\"bold\":true,\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2481944-set-formulas-for-all-rows-with-column-formulas\"},\"insert\":\"Column Formula\"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2481944-set-formulas-for-all-rows-with-column-formulas\"},\"insert\":\" \"},{\"insert\":\"so it automatically fills the column! \\n\\nHere are some references you may find useful while building this out:\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/learning-track\\\/level-3-advanced-users\\\/cross-sheet-formulas\"},\"insert\":\"Cross-sheet formulas\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/learning-track\\\/best-practice-webinars\\\/formula-foundations\"},\"insert\":\"Webinar: Formula Foundations\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Webinar: \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/learning-track\\\/best-practice-webinars\\\/essential-formulas-smartsheet-users\"},\"insert\":\"Essential Formulas for Smartsheet Users\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2476171-create-and-edit-formulas-in-smartsheet\"},\"insert\":\"Create and Edit Formulas in Smartsheet\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"\\nCheers,\\nGenevieve \\n\"}]","format":"rich","dateInserted":"2023-06-12T13:44:04+00:00","insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-11T17:39:53+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380076#Comment_380076","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/380076#Comment_380076](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/GCDDWIP57AKS\/screenshot-2023-06-12-at-14-39-04.png\"){kind=link}
![https:\/\/us.v-cdn.net\/6031209\/uploads\/XII926DPC1UT\/screenshot-2023-06-12-at-14-40-15.png<\/a><\/p>=COUNTIFS({your range}, < comma<\/p>Then HAS(@cell, <\/strong>to say look in each cell<\/em> of the previously identified range<\/p>And finally, what are you looking for? You're looking for the single value you typed in the cell to the left, the "Dropdown Column" cell. <\/p>=COUNTIFS({your range}, HAS(@cell, [Dropdown Column]@row))<\/p>The great thing about this is once you have it set up, you can right-click on the cell and turn it into a Column Formula<\/strong> <\/a>so it automatically fills the column! <\/p>Here are some references you may find useful while building this out:<\/p>Cross-sheet formulas<\/a><\/li>Webinar: Formula Foundations<\/a><\/li>Webinar: Essential Formulas for Smartsheet Users<\/a><\/li>Create and Edit Formulas in Smartsheet<\/a><\/li><\/ul>Cheers,<\/p>Genevieve<\/p>","bodyRaw":"[{\"insert\":\"Hey \"},{\"insert\":{\"mention\":{\"name\":\"KatieDi\",\"userID\":156658}}},{\"insert\":\" \\nI'd be happy to jump in here and help clarify - it sounds like you're most of the way there!\\nIn your sheet that has each individual option listed, you'll want to start building your formula by typing:\\n=COUNTIFS(\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/3W06MG6YUJQX\\\/screenshot-2023-06-12-at-14-34-52.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/3W06MG6YUJQX\\\/screenshot-2023-06-12-at-14-34-52.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\nAt this point, a pop-up helper window should appear. From here, you can select an option that says \\\"Reference Another Sheet\\\"\\n\\n\\nClick on this, and a pop-up window will let you choose \"},{\"attributes\":{\"italic\":true},\"insert\":\"which\"},{\"insert\":\" sheet you want to reference. Find that sheet, then find the column that has the multi-select values. \\nClick on the \"},{\"attributes\":{\"bold\":true},\"insert\":\"column name\"},{\"insert\":\" and it will select the entire column as a \"},{\"attributes\":{\"italic\":true},\"insert\":\"cross-sheet reference range\"},{\"insert\":\". \\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/GCDDWIP57AKS\\\/screenshot-2023-06-12-at-14-39-04.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/GCDDWIP57AKS\\\/screenshot-2023-06-12-at-14-39-04.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"You can change the \"},{\"attributes\":{\"bold\":true},\"insert\":\"name\"},{\"insert\":\" of this range in the little window at the top if you want, but you don't have to. Once you have the column highlighted, click \"},{\"attributes\":{\"bold\":true},\"insert\":\"Insert Reference\"},{\"insert\":\". That then creates a reference to this other sheet and that specific column!\\nThe rest you then build out in your current sheet:\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/XII926DPC1UT\\\/screenshot-2023-06-12-at-14-40-15.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/XII926DPC1UT\\\/screenshot-2023-06-12-at-14-40-15.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"\\n=COUNTIFS({your range}, < comma\\nThen \"},{\"attributes\":{\"bold\":true},\"insert\":\"HAS(@cell, \"},{\"insert\":\"to say look in \"},{\"attributes\":{\"italic\":true},\"insert\":\"each cell\"},{\"insert\":\" of the previously identified range\\nAnd finally, what are you looking for? You're looking for the single value you typed in the cell to the left, the \\\"Dropdown Column\\\" cell. \\n=COUNTIFS({your range}, HAS(@cell, [Dropdown Column]@row))\\n\\nThe great thing about this is once you have it set up, you can right-click on the cell and \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2481944-set-formulas-for-all-rows-with-column-formulas\"},\"insert\":\"turn it into a \"},{\"attributes\":{\"bold\":true,\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2481944-set-formulas-for-all-rows-with-column-formulas\"},\"insert\":\"Column Formula\"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2481944-set-formulas-for-all-rows-with-column-formulas\"},\"insert\":\" \"},{\"insert\":\"so it automatically fills the column! \\n\\nHere are some references you may find useful while building this out:\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/learning-track\\\/level-3-advanced-users\\\/cross-sheet-formulas\"},\"insert\":\"Cross-sheet formulas\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/learning-track\\\/best-practice-webinars\\\/formula-foundations\"},\"insert\":\"Webinar: Formula Foundations\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"Webinar: \"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/learning-track\\\/best-practice-webinars\\\/essential-formulas-smartsheet-users\"},\"insert\":\"Essential Formulas for Smartsheet Users\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"attributes\":{\"link\":\"https:\\\/\\\/help.smartsheet.com\\\/articles\\\/2476171-create-and-edit-formulas-in-smartsheet\"},\"insert\":\"Create and Edit Formulas in Smartsheet\"},{\"attributes\":{\"list\":{\"depth\":0,\"type\":\"bullet\"}},\"insert\":\"\\n\"},{\"insert\":\"\\nCheers,\\nGenevieve \\n\"}]","format":"rich","dateInserted":"2023-06-12T13:44:04+00:00","insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-11T17:39:53+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380076#Comment_380076","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/380076#Comment_380076](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/XII926DPC1UT\/screenshot-2023-06-12-at-14-40-15.png\"){kind=link}