Formulas and Data Rollup

Leo Plant

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

 

 

Christian Wells

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.

Leo Plant

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

Brian T.

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

Leo Plant

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