Averageif and match for summary of info

SarahML
SarahML ✭✭
edited 09/02/21 inFormulas and Functions

I'm trying to average the the percent of submissions for an assignment on one Smartsheet based on a teachers ID in the current sheet. Here's the formula I've tried

=AVERAGEIF({Quiz 1 Completion Avg}, MATCH([email protected], {ID }))

The results that are populating in my sheet do not match with are not correct.

We have one sheet (the Tracker Sheet) that has all the information by teacher by section and I'm trying to get a summary to show in our Teacher Summary Sheet for the course. I've looked at avg(collect( and AverageIf. There are more rows in the Tracker Sheet than the Teacher Summary Sheet.

Thanks!

Sarah

Tags:
· 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 the公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
You are very close, try this.<\/p>

=COUNTIFS([End Date]:[End Date], <= TODAY())<\/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":107098,"type":"question","name":"HAS and Children Functions","excerpt":"I have the following column1 formula: =IF(HAS(CHILDREN([Column2]@row), \"Text\"), 1, 0) which sets the value of column1 to 1 if \"Text\" is found in any children in Column2 This formula works well for a single nested file of children. However, I have a multi-level nested file (i.e. Parent1, Child1, Parent2, Child2) and it…","snippet":"I have the following column1 formula: =IF(HAS(CHILDREN([Column2]@row), \"Text\"), 1, 0) which sets the value of column1 to 1 if \"Text\" is found in any children in Column2 This…","categoryID":322,"dateInserted":"2023-06-29T18:01:34+00:00","dateUpdated":"2023-06-29T18:25:44+00:00","dateLastComment":"2023-06-29T19:48:55+00:00","insertUserID":158378,"insertUser":{"userID":158378,"name":"Mike Dufour","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Dufour","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DKvTQ7i6zPw!HGjTH6jjgiQ!wvw2rujGLYm","dateLastActive":"2023-06-29T19:48:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":158378,"lastUserID":158378,"lastUser":{"userID":158378,"name":"Mike Dufour","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Dufour","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DKvTQ7i6zPw!HGjTH6jjgiQ!wvw2rujGLYm","dateLastActive":"2023-06-29T19:48:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":40,"score":null,"hot":3376131029,"url":"https:\/\/community.smartsheet.com\/discussion\/107098\/has-and-children-functions","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107098\/has-and-children-functions","format":"Rich","lastPost":{"discussionID":107098,"commentID":383208,"name":"Re: HAS and Children Functions","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383208#Comment_383208","dateInserted":"2023-06-29T19:48:55+00:00","insertUserID":158378,"insertUser":{"userID":158378,"name":"Mike Dufour","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20Dufour","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DKvTQ7i6zPw!HGjTH6jjgiQ!wvw2rujGLYm","dateLastActive":"2023-06-29T19:48:13+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-29T19:50:03+00:00","dateAnswered":"2023-06-29T18:48:24+00:00","acceptedAnswers":[{"commentID":383194,"body":"

CHILDREN will only look at entries one level down. DESCENDANTS will look at all levels below current.<\/p>"},{"commentID":383208,"body":"

Thank you Carson, very helpful!<\/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":107085,"type":"question","name":"Using INDEX\/MATCH to pull a date and CountIF not recognizing as a date","excerpt":"Hi, I'm using INDEX\/MATCH to pull a date from one report into another based on the same ID number. If there is no match, an error message populates. =IFERROR(INDEX({Corporate CAPA Closed Effective Date}, MATCH([InfoCard Number]@row, {Corporate CAPA Closed InfoCard Number}, 0)), \"CAPA Not Closed\") Once the formula is…","snippet":"Hi, I'm using INDEX\/MATCH to pull a date from one report into another based on the same ID number. If there is no match, an error message populates. =IFERROR(INDEX({Corporate CAPA…","categoryID":322,"dateInserted":"2023-06-29T15:11:26+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T20:28:41+00:00","insertUserID":146974,"insertUser":{"userID":146974,"name":"Jack Parry","url":"https:\/\/community.smartsheet.com\/profile\/Jack%20Parry","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T20:28:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":146974,"lastUser":{"userID":146974,"name":"Jack Parry","url":"https:\/\/community.smartsheet.com\/profile\/Jack%20Parry","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T20:28:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":24,"score":null,"hot":3376123207,"url":"https:\/\/community.smartsheet.com\/discussion\/107085\/using-index-match-to-pull-a-date-and-countif-not-recognizing-as-a-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107085\/using-index-match-to-pull-a-date-and-countif-not-recognizing-as-a-date","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107085,"commentID":383212,"name":"Re: Using INDEX\/MATCH to pull a date and CountIF not recognizing as a date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383212#Comment_383212","dateInserted":"2023-06-29T20:28:41+00:00","insertUserID":146974,"insertUser":{"userID":146974,"name":"Jack Parry","url":"https:\/\/community.smartsheet.com\/profile\/Jack%20Parry","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T20:28:15+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-29T20:28:12+00:00","dateAnswered":"2023-06-29T17:10:07+00:00","acceptedAnswers":[{"commentID":383157,"body":"

The INDEX function should be retaining the data type (in this case date value). It sounds like the issue is that there is at least one blank or no-date value within the range. To accommodate that you would use:<\/p>


<\/p>

=COUNTIFS({All Sites CAPA}, CONTAINS(\"CAPA\", @cell), {All Sites CAPA Effective Date}, IFERROR(<\/strong>MONTH(@cell), 0)<\/strong> = 6, {All Sites CAPA Closed On Time?}, CONTAINS(\"CAPA Overdue\", @cell))<\/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"}]}],"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 that counts how many rows have a date in the past

    Hi! I need help with creating a formula that will count how many rows in my column called End Date have dates in the past. I've tried the following and it gives an "unparseable" error: =COUNTIFS([End Date]:[End Date], "<=" & TODAY()) Thanks in advance for your help!

  • HAS and Children Functions

    I have the following column1 formula: =IF(HAS(CHILDREN([Column2]@row), "Text"), 1, 0) which sets the value of column1 to 1 if "Text" is found in any children in Column2 This formula works well for a single nested file of children. However, I have a multi-level nested file (i.e. Parent1, Child1, Parent2, Child2) and it…

  • Using INDEX/MATCH to pull a date and CountIF not recognizing as a date

    Hi, I'm using INDEX/MATCH to pull a date from one report into another based on the same ID number. If there is no match, an error message populates. =IFERROR(INDEX({Corporate CAPA Closed Effective Date}, MATCH([InfoCard Number]@row, {Corporate CAPA Closed InfoCard Number}, 0)), "CAPA Not Closed") Once the formula is…