IFERROR INDEX COLLECT if greater than 0 issue

tchav
tchav ✭
inFormulas and Functions

I am trying to collect from a separate sheet the counts of holds if the site matches and if the count of holds is greater than 0.

I am trying to collect the Count of holds if the site matches if the count of holds is greater than 0. Can someone please help me try to collect this.

I tried below and did not work:

=IFERROR(INDEX(COLLECT({Count of Holds}, MATCH(Site@row), {Site}, ">0")))


Screenshot 2023-07-19 110100.jpg


Tags:
· Share on Facebook Share on Twitter

Best Answer

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公式手册模板!

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

 Watch video
Try nesting in an INDEX function with a specified row of 1.<\/p>

=VLOOKUP(INDEX(Code:Code, 1)<\/strong>, {Cross Sheet Reference}, .......)<\/p>"},{"commentID":390435,"body":"

Try using INDEX(Code:Code, 1) instead of $Code$1<\/p>

Note: Also, I would recommend you use INDEX\/MATCH instead of VLOOKUP. Much more reliable.<\/p>


<\/p>

I hope this helps you.<\/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":204,"urlcode":"Forms","name":"Forms"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108902,"type":"question","name":"Average with criteria","excerpt":"Hi I need some help regarding making an average. There is multiple rows with the text \"Mekanisk\" in it at \"System\" column, and a corresponding \"%\" at the \"Fullført\" colum. How do I make an average of all these uniqe % values? Thank's! :)","snippet":"Hi I need some help regarding making an average. There is multiple rows with the text \"Mekanisk\" in it at \"System\" column, and a corresponding \"%\" at the \"Fullført\" colum. How do…","categoryID":322,"dateInserted":"2023-08-14T09:59:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-14T10:52:15+00:00","insertUserID":165045,"insertUser":{"userID":165045,"name":"TorAtle Myrmel","title":"Planner","url":"https:\/\/community.smartsheet.com\/profile\/TorAtle%20Myrmel","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!DsVQA8BS3SQ!clfQx3iu-bw!1fQKS1hybet","dateLastActive":"2023-08-14T12:26:01+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-14T12:50:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":29,"score":null,"hot":3384019296,"url":"https:\/\/community.smartsheet.com\/discussion\/108902\/average-with-criteria","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108902\/average-with-criteria","format":"Rich","lastPost":{"discussionID":108902,"commentID":390420,"name":"Re: Average with criteria","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390420#Comment_390420","dateInserted":"2023-08-14T10:52:15+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-14T12:50:38+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\/B8WAXBBWC0OC\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-14T10:47:17+00:00","dateAnswered":"2023-08-14T10:42:33+00:00","acceptedAnswers":[{"commentID":390418,"body":"

Hi @TorAtle Myrmel<\/a>,<\/p>

If you're after an average for all the %ages in \"Fullført\" against rows containing \"Mekanisk\" in \"System\" then you would use the following formula:<\/p>

=AVERAGEIF([System]:[System], CONTAINS(\"Mekanisk\", @cell), [Fullført]:[Fullført])<\/p>

Example:<\/p>

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

Hope this helps, but if you've any questions or comments then just post! 😊<\/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":108884,"type":"question","name":"Join formula of two sheets","excerpt":"Hello - Shown is one sheet but two sources separate data . Can I combine the two counts anyway they have the same Primary column. I don't want to make a summary count of the two because I can't get rid of this in reporting. Can you assist me with the formula? Thanks.","snippet":"Hello - Shown is one sheet but two sources separate data . Can I combine the two counts anyway they have the same Primary column. I don't want to make a summary count of the two…","categoryID":322,"dateInserted":"2023-08-12T14:49:17+00:00","dateUpdated":null,"dateLastComment":"2023-08-13T23:44:03+00:00","insertUserID":103391,"insertUser":{"userID":103391,"name":"Marilen.Navarro103391","url":"https:\/\/community.smartsheet.com\/profile\/Marilen.Navarro103391","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-09\/60\/88\/n6088a8f5166f388fd95cfcb413bcbd0e.jpg","dateLastActive":"2023-08-14T00:03:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":103391,"lastUser":{"userID":103391,"name":"Marilen.Navarro103391","url":"https:\/\/community.smartsheet.com\/profile\/Marilen.Navarro103391","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-09\/60\/88\/n6088a8f5166f388fd95cfcb413bcbd0e.jpg","dateLastActive":"2023-08-14T00:03:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":61,"score":null,"hot":3383826200,"url":"https:\/\/community.smartsheet.com\/discussion\/108884\/join-formula-of-two-sheets","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108884\/join-formula-of-two-sheets","format":"Rich","lastPost":{"discussionID":108884,"commentID":390403,"name":"Re: Join formula of two sheets","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390403#Comment_390403","dateInserted":"2023-08-13T23:44:03+00:00","insertUserID":103391,"insertUser":{"userID":103391,"name":"Marilen.Navarro103391","url":"https:\/\/community.smartsheet.com\/profile\/Marilen.Navarro103391","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-09\/60\/88\/n6088a8f5166f388fd95cfcb413bcbd0e.jpg","dateLastActive":"2023-08-14T00:03:34+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\/EDJ96HPML3VP\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-13T21:36:39+00:00","dateAnswered":"2023-08-13T21:34:50+00:00","acceptedAnswers":[{"commentID":390398,"body":"

Here's another option:<\/p>

In your sheet, create a third section that adds the results of the first two. It would have the same rows, but the formula for each row would add the cells of section 1 + section 2.<\/p>

Add a helper column called ShowInReport as a Check Box and check all the rows in the third section. <\/p>

Create a report and tell it to show you only Section 3 (where the ShowInReport is true.<\/p>

I hope that makes sense.<\/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

  • Unique codes

    Hey all, I am trying to create a system that will check a unique code that is entered through a form and display if this code has been used already or not. I have a formula in the summary sheet that will asses if the code has been used or not: =VLOOKUP($Code$1, {Z Codes Trial Database Range 1}, 3, 0) The problem is that I…

  • Average with criteria

    Hi I need some help regarding making an average. There is multiple rows with the text "Mekanisk" in it at "System" column, and a corresponding "%" at the "Fullført" colum. How do I make an average of all these uniqe % values? Thank's! :)

  • Join formula of two sheets

    Hello - Shown is one sheet but two sources separate data . Can I combine the two counts anyway they have the same Primary column. I don't want to make a summary count of the two because I can't get rid of this in reporting. Can you assist me with the formula? Thanks.