@Bob Schweighart<\/a> you just need the COUNTIFS formula and put those 2 criterion in it. Are they distinct counts for the 4 different projects? If so, that is easier. If you are counting all 4, then you need to decide on an imbedded OR or a NOT.<\/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":112242,"type":"question","name":"Can I use Index\/Match and Left functions in one formula?","excerpt":"Hello, can someone help me with the left function within the index\/match? This is what I tried but it always returns NO MATCH: =INDEX({Master Job SO}, MATCH(LEFT([Job Number]@row,5), {Master Job #}, 0)) I also tried creating a helper column, Job Prefix, using =LEFT([Job Number]@row, 5) and using that result in the SO…","snippet":"Hello, can someone help me with the left function within the index\/match? This is what I tried but it always returns NO MATCH: =INDEX({Master Job SO}, MATCH(LEFT([Job…","categoryID":322,"dateInserted":"2023-10-26T22:39:07+00:00","dateUpdated":null,"dateLastComment":"2023-10-27T17:46:03+00:00","insertUserID":157316,"insertUser":{"userID":157316,"name":"amber.lange","url":"https:\/\/community.smartsheet.com\/profile\/amber.lange","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:44:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":157316,"lastUser":{"userID":157316,"name":"amber.lange","url":"https:\/\/community.smartsheet.com\/profile\/amber.lange","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:44:40+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":30,"score":null,"hot":3396791110,"url":"https:\/\/community.smartsheet.com\/discussion\/112242\/can-i-use-index-match-and-left-functions-in-one-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112242\/can-i-use-index-match-and-left-functions-in-one-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":112242,"commentID":402089,"name":"Re: Can I use Index\/Match and Left functions in one formula?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402089#Comment_402089","dateInserted":"2023-10-27T17:46:03+00:00","insertUserID":157316,"insertUser":{"userID":157316,"name":"amber.lange","url":"https:\/\/community.smartsheet.com\/profile\/amber.lange","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T20:44:40+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\/RTC0V6MZN24K\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/RTC0V6MZN24K\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-27T17:46:07+00:00","dateAnswered":"2023-10-27T17:30:20+00:00","acceptedAnswers":[{"commentID":402081,"body":"There we go. The [SO] column looks like it is being manually populated which is storing the data as numerical data, but the LEFT function outputs text.<\/p>
Insert a column on the reference sheet and use<\/p>
=SO@row + \"//www.santa-greenland.com/community/discussion/comment/276328/\"<\/p>
plus quote quote<\/p>
This will convert all entries into a text string so that data types match. You would then match on this helper column in your INDEX\/MATCH.<\/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":112223,"type":"question","name":"How to pull the most recent status symbol from another sheet?","excerpt":"Hello, I am trying to pull the most recent status symbol based off of the most recent date to another sheet. Basically, I am trying to get the yellow status symbol from the 20th to pull into another sheet and once someone creates a new update with the most recent date\/symbol, that symbol should be automatically updated…","snippet":"Hello, I am trying to pull the most recent status symbol based off of the most recent date to another sheet. Basically, I am trying to get the yellow status symbol from the 20th…","categoryID":322,"dateInserted":"2023-10-26T17:10:42+00:00","dateUpdated":null,"dateLastComment":"2023-10-27T20:14:26+00:00","insertUserID":169134,"insertUser":{"userID":169134,"name":"Brittaney Pizzato","url":"https:\/\/community.smartsheet.com\/profile\/Brittaney%20Pizzato","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T21:30:52+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":169134,"lastUser":{"userID":169134,"name":"Brittaney Pizzato","url":"https:\/\/community.smartsheet.com\/profile\/Brittaney%20Pizzato","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T21:30:52+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":29,"score":null,"hot":3396779108,"url":"https:\/\/community.smartsheet.com\/discussion\/112223\/how-to-pull-the-most-recent-status-symbol-from-another-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/112223\/how-to-pull-the-most-recent-status-symbol-from-another-sheet","format":"Rich","lastPost":{"discussionID":112223,"commentID":402114,"name":"Re: How to pull the most recent status symbol from another sheet?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/402114#Comment_402114","dateInserted":"2023-10-27T20:14:26+00:00","insertUserID":169134,"insertUser":{"userID":169134,"name":"Brittaney Pizzato","url":"https:\/\/community.smartsheet.com\/profile\/Brittaney%20Pizzato","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-27T21:30:52+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\/F30QLRHNUSZM\/image.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/F30QLRHNUSZM\/image.png"},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-26T20:42:55+00:00","dateAnswered":"2023-10-26T20:32:57+00:00","acceptedAnswers":[{"commentID":401926,"body":"
Hi @Brittaney Pizzato<\/a> - You would match the max date against the date column, and you don't need Collect in this scenario, so it could read as =INDEX({Project Health}, MATCH(MAX({Date}), {Date}, 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
Count two columns of a sheet that each match specific criteria Hello, I am trying to create a count that both matches one of 4 specific project initiatives and shows approved in another column. Example: Lead by Example (One Column) and the Status is "Approved" in another column.
Can I use Index/Match and Left functions in one formula? Hello, can someone help me with the left function within the index/match? This is what I tried but it always returns NO MATCH: =INDEX({Master Job SO}, MATCH(LEFT([Job Number]@row,5), {Master Job #}, 0)) I also tried creating a helper column, Job Prefix, using =LEFT([Job Number]@row, 5) and using that result in the SO…
How to pull the most recent status symbol from another sheet? Hello, I am trying to pull the most recent status symbol based off of the most recent date to another sheet. Basically, I am trying to get the yellow status symbol from the 20th to pull into another sheet and once someone creates a new update with the most recent date/symbol, that symbol should be automatically updated…
Andrée Starå ✭✭✭✭✭✭
M_Ben ✭
