Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Formulas and Data Rollup
Hi,
I am in the process of putting together a sheet that logs the amount of production each day.
As a bit of background we are an aggregate manufacturing and contracting business and what i am trying to do is have a sheet that tracks the amount of production of each product per day.
I have created a sheet with all the products listed in columns with other columns for entered by, date etc. This has been rolled up into a webform for easy entry (I am experimenting with an Appsheet app as well)
What I would like to do is rollup the data somewhere in the sheet to show
1) Totals produced forever
2) Totals produced within date ranges
3) Days a product has been produced
for 1) I have tried totalling the data at the top of the sheet which is ok but i tried suming the column but I cannot get this to work because of the other rollup data. Question is there a way to total from say row 15 until the end of the sheet so it will take into account of new entries from the web form?
For 2) Is there a formula that will calculate totals between defined dates the total of each material produced within a month? Again taking into account all cells of a column except say the first 15 (roll up data)
For 3) Is there a formula that will calculate the total days a product has been produced i.e count the number of cells that have a value other than 0 within an entire column? Say if there are 30 entries with 10 showing a value other than 0 the total would show 10. Again taking into account all cells except say the first 15 in the column(roll up data)
I have attached a screenshot to try to show what I am trying to do!
If i can get this to work my intention is to use zapier or linked cells to send this 'total' data to another sheet so I can keep track of when materials need testing as the specifications are based both on amounts and production days.
If there is a better way then please point me in the right direction
Sorry for the longwinded question! And thankyou for your help in advance
Leo
Comments
-
Hi Leo, I dont have time right now to go into a Build and suggest for you...I may later...
But to point you in some directions...
1 & 2 could be handled in a Report, have a look at that....
3 have a look athe smartsheet formula examples template, in there there are COUNTIF, SUMIF, COUNTIFS AND SUMIFS formula's that will most likely provide you the answer.
I hope this helps as a quick pointer....
good luck in the interim.
-
Hi thanks for the reply.
As I am trying to export this totalised data into another sheet live I don't know if a report function would work would it? Can the data in a report be linked to another sheet through in built linking or a zap then?
Thanks
Leo
-
Hi Leo,
"for 1) I have tried totalling the data at the top of the sheet which is ok but i tried suming the column but I cannot get this to work because of the other rollup data. Question is there a way to total from say row 15 until the end of the sheet so it will take into account of new entries from the web form?"
I am having the same problem! Please let me know if you happen to figure it out.
-Brian
-
Hi Brian,
We have worked around this by having additional 'total' columns, theN just freezing the screen at the end of the live data columns so they are easy to view. I hope this helps but if you would like me to post a screenshot or publish them just let me know.
good luck
leo
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives