Creating Summary on the Fly

claytonrlewis
claytonrlewis ✭✭✭
edited 12/09/19 inFormulas and Functions

This may be a dumb question but I have a sheet Orders that is auto-updated every morning via the Data Uploader. So the list of items changes on a daily basis... sometimes the same items are listed and sometimes new items are listed as well.

Is there a way I can create a summary sheet or grouping of duplicate items and their total quantities on the fly so that a person can look at a sheet and say... on it looks like we're gonna need to ship 100 of these items over the next two months.

Basically my sheets consists of Order #, Item#, Qty, Due Date

The order # is my primary column.

I hope this makes sense

Thanks

Clay

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Hi Terri,<\/p>

The #UNPARSABLE error<\/a> is thrown when \"the formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.\"<\/p>

ISBLANK<\/a> is a formula of it's own. It is expecting an input value between parenthesis. Here is an updated version of your formula<\/p>

=IF(NOT(ISBLANK([Frequency L4]@row)), 1, 0)<\/p>

Here is a help article<\/a> which should help with your other question regarding parent\/children rows.<\/p>

Hope this helps!<\/p>

Sal<\/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":111902,"type":"question","name":"Index Match Error","excerpt":"Are you able to help me with a formula problem? I am using a combination of MID and Right functions to pull a number from a text string.... Manager Name = Karri (800014723) Manager ID =MID(RIGHT([Manager Name]@row, 11), 2, 9) Manager ID = 800014723 -------------------------------- Now I am trying to use the Manager ID cell…","snippet":"Are you able to help me with a formula problem? I am using a combination of MID and Right functions to pull a number from a text string.... Manager Name = Karri (800014723)…","categoryID":322,"dateInserted":"2023-10-19T13:29:37+00:00","dateUpdated":null,"dateLastComment":"2023-10-19T14:37:01+00:00","insertUserID":124033,"insertUser":{"userID":124033,"name":"Katherine Britt","url":"https:\/\/community.smartsheet.com\/profile\/Katherine%20Britt","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AEdFTp5xSsXoObTLWpny9Ayq7VegQuwUIo98QzCkAhjW6A=s96-c","dateLastActive":"2023-10-19T14:35:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":124033,"lastUser":{"userID":124033,"name":"Katherine Britt","url":"https:\/\/community.smartsheet.com\/profile\/Katherine%20Britt","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AEdFTp5xSsXoObTLWpny9Ayq7VegQuwUIo98QzCkAhjW6A=s96-c","dateLastActive":"2023-10-19T14:35:29+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":21,"score":null,"hot":3395449598,"url":"https:\/\/community.smartsheet.com\/discussion\/111902\/index-match-error","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111902\/index-match-error","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":111902,"commentID":400761,"name":"Re: Index Match Error","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400761#Comment_400761","dateInserted":"2023-10-19T14:37:01+00:00","insertUserID":124033,"insertUser":{"userID":124033,"name":"Katherine Britt","url":"https:\/\/community.smartsheet.com\/profile\/Katherine%20Britt","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AEdFTp5xSsXoObTLWpny9Ayq7VegQuwUIo98QzCkAhjW6A=s96-c","dateLastActive":"2023-10-19T14:35: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-10-19T14:35:49+00:00","dateAnswered":"2023-10-19T14:03:50+00:00","acceptedAnswers":[{"commentID":400753,"body":"

@Katherine Britt<\/a> <\/p>

Your Match function is looking up the text 800014723 and trying to find a match, but in your source data it is stored as a number.<\/p>

Add the VALUE function to your Manager ID column, and that will convert it to a number.<\/p>

=VALUE(MID(RIGHT([Manager Name]@row, 11), 2, 9))<\/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":111881,"type":"question","name":"Can you create a COUNTIF formula with 3 columns?","excerpt":"In a separate metrics sheet, I am trying to count the number of pregnancy studies qualified in the month of October. I want to formula to count the row if the column contains: Type of Study contains \"Pregnancy\" Phase = \"Qualified\" Month Name Identifier (Helper Column) = 10","snippet":"In a separate metrics sheet, I am trying to count the number of pregnancy studies qualified in the month of October. I want to formula to count the row if the column contains:…","categoryID":322,"dateInserted":"2023-10-18T22:24:43+00:00","dateUpdated":"2023-10-18T22:25:36+00:00","dateLastComment":"2023-10-19T17:33:35+00:00","insertUserID":168752,"insertUser":{"userID":168752,"name":"stephcorporate","url":"https:\/\/community.smartsheet.com\/profile\/stephcorporate","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-19T17:53:07+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":168752,"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-10-19T17:32:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":21,"score":null,"hot":3395406498,"url":"https:\/\/community.smartsheet.com\/discussion\/111881\/can-you-create-a-countif-formula-with-3-columns","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111881\/can-you-create-a-countif-formula-with-3-columns","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":111881,"commentID":400806,"name":"Re: Can you create a COUNTIF formula with 3 columns?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400806#Comment_400806","dateInserted":"2023-10-19T17:33:35+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-10-19T17:32:56+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\/8PT2BKIQA87A\/example-png.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PT2BKIQA87A\/example-png.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PT2BKIQA87A\/example-png.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PT2BKIQA87A\/example-png.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PT2BKIQA87A\/example-png.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/8PT2BKIQA87A\/example-png.png"},"alt":"Example.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-19T17:07:52+00:00","dateAnswered":"2023-10-19T02:03:50+00:00","acceptedAnswers":[{"commentID":400693,"body":"

You will need to set up references to point to your source sheet, then this should work.<\/p>

=COUNTIFS({Type of Study]}, HAS(@cell, \"Pregnancy\"), {Phase}, \"Qualified\", {Month Name Identifier (Helper Column)}, VALUE(@cell) = 10)<\/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