通常使用条件统计计算和Often,

steinkj
steinkj ✭✭✭✭
edited 06/27/23 inFormulas and Functions

however I can't get it to work the Often is also counting the Very Often:

=COUNTIF([Tools & Resources (SE-2)]8:[Tools & Resources (SE-2)]12, HAS("Often", @cell), NOT(HAS("Very", @cell)))

Best Answers

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
@jcabaniss<\/a> , swap out this:<\/p>

=[Target End Date]1<\/p>

with this:<\/p>

=Index([Target End Date]:[Target End Date], 1)<\/p>

the second value in an index function is the row—usually we make this a MATCH lookup function but it can also be a number.<\/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":107112,"type":"question","name":"How to we exclude a specific name from a DISTINCT LIST USER formula","excerpt":"I would like to exclude a specific user name from this formula. The formula is working fine, and it is returning a distinct list of user names. However, we need to exclude certain users, that are no longer in the group. For example, how do I say to exclude user \"Joe Black\" from this list? =IFERROR(INDEX(DISTINCT({Trial…","snippet":"I would like to exclude a specific user name from this formula. The formula is working fine, and it is returning a distinct list of user names. However, we need to exclude certain…","categoryID":322,"dateInserted":"2023-06-29T22:30:22+00:00","dateUpdated":"2023-06-29T22:33:31+00:00","dateLastComment":"2023-06-30T13:51:12+00:00","insertUserID":157974,"insertUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T14:54:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":157974,"lastUserID":157974,"lastUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T14:54:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":66,"score":null,"hot":3376213894,"url":"https:\/\/community.smartsheet.com\/discussion\/107112\/how-to-we-exclude-a-specific-name-from-a-distinct-list-user-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107112\/how-to-we-exclude-a-specific-name-from-a-distinct-list-user-formula","format":"Rich","lastPost":{"discussionID":107112,"commentID":383323,"name":"Re: How to we exclude a specific name from a DISTINCT LIST USER formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383323#Comment_383323","dateInserted":"2023-06-30T13:51:12+00:00","insertUserID":157974,"insertUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T14:54:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-30T13:43:49+00:00","dateAnswered":"2023-06-30T12:01:20+00:00","acceptedAnswers":[{"commentID":383296,"body":"

Hi @Filippo<\/a> <\/p>

You can use a COLLECT Function<\/a> to filter results, for example:<\/p>

=IFERROR(INDEX(DISTINCT(COLLECT(<\/strong>{Trial Lead}, {Trial Lead}, <> \"Joe Black\")<\/strong>), [Unique Row ID]@row, 0), \"//www.santa-greenland.com/community/discussion/106984/\")<\/p>

An alternative would be to use a Report and Group by the Trial Lead column, ignoring out the users you don't want by adding them to the filter criteria in the Report.<\/p>

Cheers,<\/p>

Genevieve<\/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":107118,"type":"question","name":"I am trying to replicate a formula, but it is changing all the formulas on the page","excerpt":"I am trying to create the formula in the \"Activities\" row, to the \"Issues\/Pain points\" row with a different reference column. I also need to change the reference sheet, but I want to get this formula right in the first instance. Can anyone help?","snippet":"I am trying to create the formula in the \"Activities\" row, to the \"Issues\/Pain points\" row with a different reference column. I also need to change the reference sheet, but I want…","categoryID":322,"dateInserted":"2023-06-30T00:39:34+00:00","dateUpdated":null,"dateLastComment":"2023-06-30T04:19:52+00:00","insertUserID":163030,"insertUser":{"userID":163030,"name":"Tamar","url":"https:\/\/community.smartsheet.com\/profile\/Tamar","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T04:37:17+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":122388,"lastUser":{"userID":122388,"name":"Matt Johnson","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Matt%20Johnson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZX2A39HRG0VO\/nLJJDDQXUVD4H.JPG","dateLastActive":"2023-06-30T15:50:54+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":25,"score":null,"hot":3376184966,"url":"https:\/\/community.smartsheet.com\/discussion\/107118\/i-am-trying-to-replicate-a-formula-but-it-is-changing-all-the-formulas-on-the-page","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107118\/i-am-trying-to-replicate-a-formula-but-it-is-changing-all-the-formulas-on-the-page","format":"Rich","lastPost":{"discussionID":107118,"commentID":383258,"name":"Re: I am trying to replicate a formula, but it is changing all the formulas on the page","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383258#Comment_383258","dateInserted":"2023-06-30T04:19:52+00:00","insertUserID":122388,"insertUser":{"userID":122388,"name":"Matt Johnson","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Matt%20Johnson","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/ZX2A39HRG0VO\/nLJJDDQXUVD4H.JPG","dateLastActive":"2023-06-30T15:50:54+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"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\/HUYT81EVYVIV\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-30T04:41:02+00:00","dateAnswered":"2023-06-30T04:19:52+00:00","acceptedAnswers":[{"commentID":383258,"body":"

Hi @Tamar<\/a> <\/p>

I think this is happening becuase you are editing a range that exists in the original location. Instead of editing the range in the new location, be sure to delete the entire range and then click in the place where it was in the formula. Then choose another range. <\/p>

I hope that helps.<\/p>

Matt<\/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