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

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.