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