How do I count the reason for the consult, AND the year it took place?
I tried to amend this formula to report by year. Below is the formula I tried, which didn't work. The reasons are part of a drop down on another sheet. The consult date is on the same sheet, but the full date is in the cell, 10/12/2017, 11/1/2017, etc.
My current formula in the TOTALS to DATE is:=COUNTIF({Reason for Consult}, CONTAINS("fever", @cell)) This formula does work.
Here is what I tried in order to count the number of times fever AND the year 2017 were true.
=COUNTIFS({Reason for Consult}, CONTAINS("fever", @cell), ({Consult Date}, AND((@cell>=DATE(2017, 1,1), @cell<=DATE((2017, 12, 31))))
Best Answer
-
Razetto ✭✭✭✭✭@kristenryanHi! , I wonder if using YEAR and HAS give you an output =COUNTIFS({Reason for Consult}, HAS(@cell,"fever"), {Consult Date}, YEAR(@cell)=2017)
Answers
-
Razetto ✭✭✭✭✭
@kristenryanHi! , I wonder if using YEAR and HAS give you an output =COUNTIFS({Reason for Consult}, HAS(@cell,"fever"), {Consult Date}, YEAR(@cell)=2017)
-
@Razettothat worked perfectly. Thank you for your assistance.
Help Article Resources
Categories
=IF(OR(CONTAINS(\"Insert Word\", [Column 2]@row), [Column 1]@row = 1), \"Green\", IF(CONTAINS(\"Specific Word\", [Column 3]@row), \"Yellow\", \"Red\"))<\/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":110511,"type":"question","name":"Row # on Another Sheet","excerpt":"Hey! First thanks in advance for looking. Is it possible to get the row # on another sheet? I want to know the Row # for New: Release Update Sheet. New: Release Update Sheet Release (would require Is Parent to be checked) Row ID (Auto Number) Release List (This sheet is where the formula would go) Releases Auto (Auto…","snippet":"Hey! First thanks in advance for looking. Is it possible to get the row # on another sheet? I want to know the Row # for New: Release Update Sheet. New: Release Update Sheet…","categoryID":322,"dateInserted":"2023-09-20T15:37:17+00:00","dateUpdated":null,"dateLastComment":"2023-09-20T20:04:14+00:00","insertUserID":150737,"insertUser":{"userID":150737,"name":"Sherry Fox","title":"Project Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Sherry%20Fox","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zqHrM0vl3HM!!QfXUMYHRSc0","dateLastActive":"2023-09-20T18:34:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":150737,"lastUser":{"userID":150737,"name":"Sherry Fox","title":"Project Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Sherry%20Fox","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zqHrM0vl3HM!!QfXUMYHRSc0","dateLastActive":"2023-09-20T18:34:46+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":25,"score":null,"hot":3390465691,"url":"https:\/\/community.smartsheet.com\/discussion\/110511\/row-on-another-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110511\/row-on-another-sheet","format":"Rich","tagIDs":[254,319],"lastPost":{"discussionID":110511,"commentID":396329,"name":"Re: Row # on Another Sheet","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/396329#Comment_396329","dateInserted":"2023-09-20T20:04:14+00:00","insertUserID":150737,"insertUser":{"userID":150737,"name":"Sherry Fox","title":"Project Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Sherry%20Fox","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!zqHrM0vl3HM!!QfXUMYHRSc0","dateLastActive":"2023-09-20T18:34:46+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-09-20T23:51:41+00:00","dateAnswered":"2023-09-20T17:34:52+00:00","acceptedAnswers":[{"commentID":396251,"body":"
This sounds possible but I need some more info to address this.<\/p>
It sounds like you want your Release List <\/strong>Sheet <\/strong>to show the *Row ID* value for a row in the Release Update Sheet <\/strong>provided the Parent row in the Release List <\/strong>is checked?<\/p> I believe you could do this by using COLLECT() to pull in the Row ID values from Release Sheet <\/strong>that match a common criteria (i.e., a lookup value) that is shared across both Sheets (do you have such a value)? With COLLECT() you could limit so that you are pulling in all rows where Release is checked in the parent (might need a helper column to facilitate this) AND the unique identifier is matched.<\/p> Something like the below, with the caveat I have not seen your Sheets so can't say for sure this will work!<\/p> =INDEX(COLLECT({Row ID from Release Sheet<\/strong>}, {Helper Column in Release Sheet <\/strong>that checks when Parent is checked}, 1, [Unique Identifier Column]@row, {Column w\/ matching unique identifiers in Release Sheet<\/strong>}),1) <\/p> Dan Palenchar | <\/strong>School of Sheets Solutions Consulting<\/a> (Smartsheet Aligned Gold Partner)<\/p> Smartsheet Consulting Inquiries: schoolofsheets.com\/workwithus<\/a><\/p> Smartsheet Tutorial Videos: schoolofsheets.com\/youtube<\/a><\/p>"},{"commentID":396329,"body":" @SoS | Dan Palenchar<\/a> ,<\/p> Yes you are correct. And I do have a unique identifier as you mentioned. The Release # consists of primarily numeric characters, although there are times the numeric values are identic, so letters (or words) are added to ensure the values remain unique.<\/p> New: Release Update Sheet: <\/em><\/strong>Release<\/p> Release Sheet: <\/strong>Releases<\/p> I entered the formula as follows and below the formula is each component, and which sheet it comes from. I obviously messed up something, as it gives an error of #INCORRECT ARGUMENT SET<\/em><\/strong><\/p> =INDEX(COLLECT({Row ID}, {Is Parent}, 1, Releases@row, {Release #}), 1)<\/p> Row ID - New: Release Update Sheet<\/strong><\/p> Is Parent - New: Release Update Sheet<\/strong><\/p> Releases@row - Release Sheet (formula is on this sheet)<\/strong><\/p> Release # - New: Release Update Sheet<\/strong><\/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"},{"tagID":319,"urlcode":"functionality","name":"functionality"}]},{"discussionID":110522,"type":"question","name":"Can you add a Sheet name to a formula?","excerpt":"Can you include the sheet name in a formula? Not reference the Sheet. The Sheet is already referenced in the INDEX\/MATCH formula. I could manually add the sheet name to the formula, but wondering if it's possible for the formula to automatically pick up on the Sheet name.","snippet":"Can you include the sheet name in a formula? Not reference the Sheet. The Sheet is already referenced in the INDEX\/MATCH formula. I could manually add the sheet name to the…","categoryID":322,"dateInserted":"2023-09-20T17:37:03+00:00","dateUpdated":"2023-09-20T23:19:38+00:00","dateLastComment":"2023-09-20T17:43:02+00:00","insertUserID":159073,"insertUser":{"userID":159073,"name":"SJTA","url":"https:\/\/community.smartsheet.com\/profile\/SJTA","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-20T17:48:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":166028,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-09-20T23:20:26+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":18,"score":null,"hot":3390465005,"url":"https:\/\/community.smartsheet.com\/discussion\/110522\/can-you-add-a-sheet-name-to-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110522\/can-you-add-a-sheet-name-to-a-formula","format":"Rich","lastPost":{"discussionID":110522,"commentID":396256,"name":"Re: Can you add a Sheet name to a formula?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/396256#Comment_396256","dateInserted":"2023-09-20T17:43:02+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-09-20T23:20: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-09-20T17:49:27+00:00","dateAnswered":"2023-09-20T17:40:10+00:00","acceptedAnswers":[{"commentID":396254,"body":" Hi @SJTA<\/a> <\/p> I hope you're well and safe!<\/p> Unfortunately, it's not possible now, but it's an excellent idea!<\/p> Please submit this as a Product Feedback or Idea <\/strong>(If it hasn't been added already)<\/em><\/strong> when you have a moment.<\/strong><\/a><\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":[]}">
