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

Leo Plant
Leo Plant
edited 12/09/19 in Archived 2016 Posts

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

 

 

2016-02-10 (2).png

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

This discussion has been closed.