Trouble with formulas not applying to new info coming from forms

steve50951

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.

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

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