Trouble with formulas not applying to new info coming from forms

I created a form to capture data into a sheet. i have the form sending new info to the bottom of the form. when the data comes in from the form it puts the rows below the formulas, so the formulas are not applied. It's a sum formula, so i need it to capture the total amount of totals coming in from the form. how do i get the formulas to apply to the data coming in from the form.

Screen Shot 2019-04-24 at 2.13.17 PM.png

Find more posts tagged with

Forms

Formulas

Comments

Shawn Connelly

I would place your formulas in separate columns to the right side of the sheet, and make sure that the formulas reference the entire column that you're trying to sum, and not just a specific cell range. It looks like you're trying to sum a specific range instead of just using [Amount]:[Amount].

Andrée Starå

Hi Steve,

I agree with Shawn, and that's the best practice to be sure that the calculations would be correct.

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

steve50951

Shawn,

I'm not sure what you mean by placing the formulas in separate columns on the right side of the sheet. I did edit the formula in the "Amount" column.

Here's what im using

=SUM(Amount:Amount)

The formula seems to include the entire column. I think that's were after. Isn't it?

Thanks for your help.

SGF

Screen Shot 2019-04-25 at 8.03.39 AM.png

Andrée Starå

Hi Steve,

If you have the formula in the same column it won't work so you'll have to have it in another cell in another column.

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

To get formulas to populate on new rows when a form creates a new entry, the formulas will have to be on the row above it before the form is submitted.

To INCLUDE new rows in existing formulas looking down a column, you will need to move your formula to another column and reference the entire column. If you specify a range, it will only look at that range.

JoeC.

Steve, if I understand your original post correctly, all you want to do is sum the entirety of a column, especially as new rows are added.

The most reliable way to do this is to create a helper sheet (grid) and designate one column to hold the value. It is in this column that you reference your main sheet and simply sum the Amount column. In the helper sheet, when you start typing the formula, you will see the ability to "Reference Another Sheet"...just click on it, select your main sheet, then click on the Amount column header. It should be that easy.

The advantage of using a helper sheet, instead of sticking the sum into a column in the main sheet, is that you aren't mixing row level information (detail) with summary level information, though either way will work.

You can use the helper sheet store all of your summary or calculation information, especially more complex/conditional calculations, and make it easier to display this information on a dashboard, etc. It's a tiny bit extra work, but I have always found it much more helpful.

steve50951

JoeC,

Thank you very much for your help. This helps tremendously. It is a little bit extra, but this will give me exactly what i need.

Awesome!

Thanks again,

SGF

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?