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")

    thinkspi.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 something like this...<\/p>


<\/p>

Months:<\/p>

=INT([Duration Days]@row \/ 30)<\/p>


<\/p>

Weeks:<\/p>

=INT(([Duration Days]@row - (Months@row * 30)) \/ 7)<\/p>


<\/p>

Days:<\/p>

=[Duration Days]@row - ((Weeks@row * 7) + (Months@row * 30))<\/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":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":324,"urlcode":"community-platform","name":"Community Platform"}]},{"discussionID":107875,"type":"question","name":"IF Statement with multiple dates an count.","excerpt":"Hello, I have a large sheet that I am trying to calculate several dates to populate dependent on a Count in a row field (ie - a count of 0 would generate a date being pulled from one column, and a count of one generating a date from another column). Columns in Question: \"Was Rejected\" - this is a count on either it has…","snippet":"Hello, I have a large sheet that I am trying to calculate several dates to populate dependent on a Count in a row field (ie - a count of 0 would generate a date being pulled from…","categoryID":322,"dateInserted":"2023-07-20T18:16:53+00:00","dateUpdated":null,"dateLastComment":"2023-07-20T20:15:17+00:00","insertUserID":163694,"insertUser":{"userID":163694,"name":"Jivi3","title":"Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Jivi3","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-20T20:47:46+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-07-20T21:15:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":24,"score":null,"hot":3379762930,"url":"https:\/\/community.smartsheet.com\/discussion\/107875\/if-statement-with-multiple-dates-an-count","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107875\/if-statement-with-multiple-dates-an-count","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107875,"commentID":386276,"name":"Re: IF Statement with multiple dates an count.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386276#Comment_386276","dateInserted":"2023-07-20T20:15:17+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-07-20T21:15:47+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-07-20T20:13:37+00:00","dateAnswered":"2023-07-20T18:21:07+00:00","acceptedAnswers":[{"commentID":386241,"body":"

Make sure all three columns are set as date type columns.<\/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":107871,"type":"question","name":"Want to compare values in 3 columns to return a value in 4th column","excerpt":"Trying to sum up all many active projects are over due. Can add up my active projects with formula: =if(and(type@row=\"project\",status@row=\"in progress),1,0) but now I want to add the criteria of target completion date is late. Tried the following but get the dreaded \"unparseable\" message... =if((and(\"target completion…","snippet":"Trying to sum up all many active projects are over due. Can add up my active projects with formula: =if(and(type@row=\"project\",status@row=\"in progress),1,0) but now I want to add…","categoryID":322,"dateInserted":"2023-07-20T17:34:51+00:00","dateUpdated":null,"dateLastComment":"2023-07-20T17:52:55+00:00","insertUserID":139408,"insertUser":{"userID":139408,"name":"BJTidi","url":"https:\/\/community.smartsheet.com\/profile\/BJTidi","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-20T17:53:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":139408,"lastUser":{"userID":139408,"name":"BJTidi","url":"https:\/\/community.smartsheet.com\/profile\/BJTidi","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-20T17:53:29+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":27,"score":null,"hot":3379751266,"url":"https:\/\/community.smartsheet.com\/discussion\/107871\/want-to-compare-values-in-3-columns-to-return-a-value-in-4th-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107871\/want-to-compare-values-in-3-columns-to-return-a-value-in-4th-column","format":"Rich","lastPost":{"discussionID":107871,"commentID":386225,"name":"Re: Want to compare values in 3 columns to return a value in 4th column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386225#Comment_386225","dateInserted":"2023-07-20T17:52:55+00:00","insertUserID":139408,"insertUser":{"userID":139408,"name":"BJTidi","url":"https:\/\/community.smartsheet.com\/profile\/BJTidi","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-20T17:53:29+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-07-20T17:53:25+00:00","dateAnswered":"2023-07-20T17:47:21+00:00","acceptedAnswers":[{"commentID":386222,"body":"

I am not sure I am interpreting your request correctly. Is this what you are trying to accomplish?<\/p>

=IF(AND(type@row=\"project\", status@row=\"in progress\", [target completion date]@row < TODAY()), 1, 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

  • Formula to show duration days in Months, Weeks, and Days

    I have a duration in days column and I need to show what that duration is in months, weeks and days. Assuming a month is 30 days and a week is 7 days. The attached blue columns are what I want to calculate with a formula (manually entered now to show what I want to see). For example, if something is 38 days, I want it to…

  • IF Statement with multiple dates an count.

    Hello, I have a large sheet that I am trying to calculate several dates to populate dependent on a Count in a row field (ie - a count of 0 would generate a date being pulled from one column, and a count of one generating a date from another column). Columns in Question: "Was Rejected" - this is a count on either it has…

  • Want to compare values in 3 columns to return a value in 4th column

    Trying to sum up all many active projects are over due. Can add up my active projects with formula: =if(and(type@row="project",status@row="in progress),1,0) but now I want to add the criteria of target completion date is late. Tried the following but get the dreaded "unparseable" message... =if((and("target completion…