Avg number of selections per condition

Leah Schmid
Leah Schmid ✭
inFormulas and Functions

Hi there,

I need some help. I'm trying to calculate the avg number of issues per condition in our master tracker. The issues are in a multi-value cell {Rev Rec Master Tracker Range 1} while the condition is a single-value cell in {Rev Rec Master Tracker Range 2} with values either as "Backlog" or "Net New". I'd like to find out the average # of issues in Backlog and average # of issues in Net New.

Here is what I have which I know isn't calculating the fields correctly. In my example for backlog there are 3 issues noted for 2 items in the backlog. So the avg should be 3/2 = 1.5

=IF(HAS({Rev Rec Master Tracker Range 2}, "Backlog"), COUNTM(HAS({Rev Rec Master Tracker Range 1}, {Rev Rec Master Tracker Range 2}, "Backlog"), 0)) / COUNTIF({Rev Rec Master Tracker Range 2}, "Backlog")

Appreciate any help!

Thanks!

Tags:
· Share on Facebook Share on Twitter

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    所以对于“积压”你会希望它数3活动on Plan Notes and then divide by 2 "Backlog" entries? If so, try this:

    =COUNTM(COLLECT({Action Plan Notes}, {RR Type}, @cell = "Backlog")) / COUNTIFS({RR Type}, @cell = "Backlog")

    10xViz.com

    · Share on Facebook Share on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Try removing one of the closing parenthesis after the DATE function.<\/p>"},{"commentID":388644,"body":"

Thanks! That sorted it!😀<\/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":[]},{"discussionID":108438,"type":"question","name":"NETWORKDAYS WITH AN IF??","excerpt":"Can anyone help with a formula to track the number of days between 2 separate columns with the IF of stopping the count once a date is populated in one of those columns?","snippet":"Can anyone help with a formula to track the number of days between 2 separate columns with the IF of stopping the count once a date is populated in one of those columns?","categoryID":322,"dateInserted":"2023-08-02T19:37:00+00:00","dateUpdated":null,"dateLastComment":"2023-08-02T20:11:28+00:00","insertUserID":164426,"insertUser":{"userID":164426,"name":"JT_22002","url":"https:\/\/community.smartsheet.com\/profile\/JT_22002","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-03T11:32:53+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-03T12:32:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":18,"score":null,"hot":3382012708,"url":"https:\/\/community.smartsheet.com\/discussion\/108438\/networkdays-with-an-if","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108438\/networkdays-with-an-if","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108438,"commentID":388600,"name":"Re: NETWORKDAYS WITH AN IF??","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388600#Comment_388600","dateInserted":"2023-08-02T20:11:28+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-03T12:32:35+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-03T11:34:23+00:00","dateAnswered":"2023-08-02T20:11:28+00:00","acceptedAnswers":[{"commentID":388600,"body":"

I'm not sure I understand the goal. If you want to see the days between two column, you will need to already have a date in each of those columns. This seems to contradict stopping the count once one of the columns is populated. Can you provide more detail, or maybe a screenshot\/mockup of what you are looking for?<\/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":108410,"type":"question","name":"Using the \"Created Date\" field in formulas","excerpt":"Hello - I am creating a number of fields in various sheets to calculate lead times. One KPI we want to track is the life cycle of a project. I have added the \"Created Date\" field to all sheets and have an additional field that populates an Invoiced Date. I want a new field that will calculate the number of days between the…","snippet":"Hello - I am creating a number of fields in various sheets to calculate lead times. One KPI we want to track is the life cycle of a project. I have added the \"Created Date\" field…","categoryID":322,"dateInserted":"2023-08-02T13:45:01+00:00","dateUpdated":null,"dateLastComment":"2023-08-02T17:51:15+00:00","insertUserID":141394,"insertUser":{"userID":141394,"name":"KGetchell","url":"https:\/\/community.smartsheet.com\/profile\/KGetchell","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-02T18:07:47+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-02T18:59:20+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":30,"score":null,"hot":3381985576,"url":"https:\/\/community.smartsheet.com\/discussion\/108410\/using-the-created-date-field-in-formulas","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108410\/using-the-created-date-field-in-formulas","format":"Rich","lastPost":{"discussionID":108410,"commentID":388557,"name":"Re: Using the \"Created Date\" field in formulas","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388557#Comment_388557","dateInserted":"2023-08-02T17:51:15+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-02T18:59:20+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\/C0IGNY6CEA25\/capture-jpg.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.JPG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-03T09:13:22+00:00","dateAnswered":"2023-08-02T15:09:48+00:00","acceptedAnswers":[{"commentID":388501,"body":"

The Created (date) field is not the issue in this instance. The problem is the data in your Invoiced Date column is actually text strings instead of date values.<\/p>


<\/p>

Try putting this into a date type column and using this new column in place of the Invoiced Date column in your original formula.<\/p>

=DATE(VALUE(MID([Invoiced Date (EP)]@row, FIND(\" \", [Invoiced Date (EP]@row) - 4, 4)), VALUE(LEFT([Invoiced Date (EP)]@row, FIND(\"\/\", [Invoiced Date (EP)]@row) - 1)), VALUE(MID([Invoiced Date (EP)]@row, FIND(\"\/\", [Invoiced Date (EP)]@row) + 1, FIND(\"\/\", [Invoiced Date (EP)]@row, FIND(\"\/\", [Invoiced Date (EP)]@row) + 1) - (FIND(\"\/\", [Invoiced Date (EP)]@row) + 1))))<\/p>"},{"commentID":388539,"body":"

Double check that you are putting it in a date type column. Can you also provide a screenshot of those same rows with the errors but also showing the Invoiced Date? It is working for me.<\/p>


<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


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

  • Date formula to set cut off period

    I am trying to set a date formula to for the following statement; if the [Next Due Date] is <= 27/07/2023 return "Performed" Example: =IF(ISBLANK([Last inspection]@row), "", IF(DATE(2023,7,27)) < [Next Due Date]@row, "Overdue", "Performed"))

  • NETWORKDAYS WITH AN IF??

    Can anyone help with a formula to track the number of days between 2 separate columns with the IF of stopping the count once a date is populated in one of those columns?

  • Using the "Created Date" field in formulas

    Hello - I am creating a number of fields in various sheets to calculate lead times. One KPI we want to track is the life cycle of a project. I have added the "Created Date" field to all sheets and have an additional field that populates an Invoiced Date. I want a new field that will calculate the number of days between the…