@hannahstayo<\/a>,<\/p>If you change the \"PP01\" in your formula to [Unit]@row, then it will reference the contents of the Unit cell on the row.<\/p>
You can then use this as a column formula, though you may wish to enfold it in an IFERROR so you don't get errors on the month row at the top, for example.<\/p>
=IFERROR(COUNTIFS({1. LOS, ADR and Occupancy data Range 6}, [Unit]@row, {1. LOS, ADR and Occupancy data Range 7}, \"1\"),\"//www.santa-greenland.com/community/discussion/68187/\")<\/p>
Hope this helps, but if you've any problems\/questions then just post! 🙂<\/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":111737,"type":"question","name":"Looking to capture the week# within a month","excerpt":"I am needing to identify a specific week# within the month, for instance 10\/16\/2023 (US) would be the 3rd week of October. It would need to be based on the Monday of the week, so if the day was 11\/1\/2023, Wednesday then I would think I need the week to be 5th week of October. Or does anyone have a different viewpoint?","snippet":"I am needing to identify a specific week# within the month, for instance 10\/16\/2023 (US) would be the 3rd week of October. It would need to be based on the Monday of the week, so…","categoryID":322,"dateInserted":"2023-10-16T21:34:59+00:00","dateUpdated":null,"dateLastComment":"2023-10-17T13:19:30+00:00","insertUserID":131545,"insertUser":{"userID":131545,"name":"Paul Reeves","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Reeves","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/2B7GJH1T1ULO\/n6MUQUILM6FJP.jpg","dateLastActive":"2023-10-17T13:07:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":131545,"lastUser":{"userID":131545,"name":"Paul Reeves","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Reeves","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/2B7GJH1T1ULO\/n6MUQUILM6FJP.jpg","dateLastActive":"2023-10-17T13:07:16+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":20,"score":null,"hot":3395043269,"url":"https:\/\/community.smartsheet.com\/discussion\/111737\/looking-to-capture-the-week-within-a-month","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111737\/looking-to-capture-the-week-within-a-month","format":"Rich","lastPost":{"discussionID":111737,"commentID":400321,"name":"Re: Looking to capture the week# within a month","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400321#Comment_400321","dateInserted":"2023-10-17T13:19:30+00:00","insertUserID":131545,"insertUser":{"userID":131545,"name":"Paul Reeves","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Reeves","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/2B7GJH1T1ULO\/n6MUQUILM6FJP.jpg","dateLastActive":"2023-10-17T13:07:16+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-17T13:10:06+00:00","dateAnswered":"2023-10-17T03:19:46+00:00","acceptedAnswers":[{"commentID":400263,"body":"There is likely an easier (or at least shorter) way to do this, but this seems to work. The source date would be in [Day]<\/p>
=IF(MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 28)) = MONTH([Day]@row), \"5\", IF(MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 21)) = MONTH([Day]@row), \"4\", IF(MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 14)) = MONTH([Day]@row), \"3\", IF(MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 7)) = MONTH([Day]@row), \"2\", IF(MONTH(IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2)) = MONTH([Day]@row), \"1\", IF(MONTH(IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2)) = MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 28)), \"5\", IF(MONTH(IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2)) = MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 21)), \"4\", IF(MONTH(IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2)) = MONTH((IF(WEEKDAY([Day]@row) = 1, [Day]@row - 6, [Day]@row - WEEKDAY([Day]@row) + 2) - 14)), \"3\"))))))))<\/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":111726,"type":"question","name":"Conditional INDEX\/MATCH Question","excerpt":"Hi there, I am using the following formula to pull data from a second sheet but realized when \"Medicine\" is selected in the Entity\/Dept column, I would need to match information from a Division column instead. =INDEX({Notice Name}, MATCH([JHU Entity\/Dept., JHHS or JHH Dept., Sub]@row, {Department}, 0)) I thought a…","snippet":"Hi there, I am using the following formula to pull data from a second sheet but realized when \"Medicine\" is selected in the Entity\/Dept column, I would need to match information…","categoryID":322,"dateInserted":"2023-10-16T19:10:31+00:00","dateUpdated":null,"dateLastComment":"2023-10-17T13:57:32+00:00","insertUserID":130598,"insertUser":{"userID":130598,"name":"Alexandra Prescott","url":"https:\/\/community.smartsheet.com\/profile\/Alexandra%20Prescott","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pDNQKWBSqLQ!3UFXv_NEiPw!dYtzUI6YTsZ","dateLastActive":"2023-10-17T14:22:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":120260,"lastUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-10-17T13:57:15+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":215,"score":null,"hot":3395036283,"url":"https:\/\/community.smartsheet.com\/discussion\/111726\/conditional-index-match-question","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111726\/conditional-index-match-question","format":"Rich","lastPost":{"discussionID":111726,"commentID":400339,"name":"Re: Conditional INDEX\/MATCH Question","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400339#Comment_400339","dateInserted":"2023-10-17T13:57:32+00:00","insertUserID":120260,"insertUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2023-10-17T13:57:15+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers Alumni"}},"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\/X4SIUKHFKEAG\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/X4SIUKHFKEAG\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/X4SIUKHFKEAG\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/X4SIUKHFKEAG\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/X4SIUKHFKEAG\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/X4SIUKHFKEAG\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-17T13:42:48+00:00","dateAnswered":"2023-10-16T20:06:45+00:00","acceptedAnswers":[{"commentID":400226,"body":"
I think you just need to move where the condition is coming from. You first need to do the IF, and then have 2 different INDEX\/MATCH formulas based on the return. This should work:<\/p>
=IF([JHU Entity\/Dept., JHHS or JHH Dept., Sub]@row = “Medicine”, INDEX({Notice Name}, MATCH([JHU Department of Medicine Division]@row, {Division}, 0)), INDEX({Notice Name}, MATCH([JHU Entity\/Dept., JHHS or JHH Dept., Sub]@row, {Department}, 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":[]}],"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
Use a cell reference as a criteria in sumif and countif formulas I have a set of data, as shown below, and I am using formulas to pull data from the columns nights, ADR and total. I am using COUNTIFS and SUMIS formulas to collect the data, which are working well. The issue I have is that I need to replicate this data and tables for multiple different buildings and units. Right now I am…
Looking to capture the week# within a month I am needing to identify a specific week# within the month, for instance 10/16/2023 (US) would be the 3rd week of October. It would need to be based on the Monday of the week, so if the day was 11/1/2023, Wednesday then I would think I need the week to be 5th week of October. Or does anyone have a different viewpoint?
Conditional INDEX/MATCH Question Hi there, I am using the following formula to pull data from a second sheet but realized when "Medicine" is selected in the Entity/Dept column, I would need to match information from a Division column instead. =INDEX({Notice Name}, MATCH([JHU Entity/Dept., JHHS or JHH Dept., Sub]@row, {Department}, 0)) I thought a…
Andrée Starå ✭✭✭✭✭✭
