Auto sum in form?

ahrenm1

Is it possible to have a field in a Smartsheet form auto calculate a sum of previous fields in the same form?

For example: On an invoice form: If $500 was spent on supplies, $400 was spent on travel, $150 was spent on other allowable, can we have that auto-calculate into the Total field? Right now, submitters have to calculate the total by hand and it would help erase calculation errors, if this was possible. Thank you.

Cory Page

You should be able to add a column formula that sums all of the columns, as the forms drop it would just add things up. You would have to use a sumifs if you had multiple web forms with the same invoice number but it should work as well. Maybe you could drop in some simple dummy data so that we can take a look?

ahrenm1

Unfortunately, we need the total to show on the actual form before it is submitted. The individuals submitting the forms do not have access to the sheet that it flows into and would be the ones benefitting from a total field.

Cory Page

That stinks, I am sure there isn't a way to add sum in a webform. I doubt it will work for you but I have been messing around with the Dynamic View's that SS has now. They are actually really good at controlling data access, its possible that you may be able to swap out the web forms with a Dynamic view that matches the fields of a webform but with an added bonus of a total sum field. It could be a pain if you have a lot of different users but I would think anything around 5 or 10 should be pretty straight forward. If you design the filters right I would think you could have a blank sheet anytime they go in to submit? I haven't used it enough to provide too much advice other than it has a lot of potential.

ahrenm1

Thank you for the idea! We have probably 100+ users that submit regularly, so not sure that will work for us. I appreciate your willingness to help.

Cory Page

Yea, it was the permissions part that I thought might give you some trouble. It was doable but unless you had some kind of group permissions set up it would take some time to get everyone access. Webforms are so nice because it can be open to the world if you want. I still think you might find an answer in Dynamic views if you feel the gain of totaling is worth a few hours of build time. Good luck, 100+ webform users regularly is a large amount of inbound data to track and keep clean for sure.