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

edited 12/09/19

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

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