Formulas Including New Rows

hquinn

Hi, 

(Sorry if this has already been asked and resolved, I couldn't find anything). 

I currently use smartsheet for our team's training log. In doing so, there are several columns that require numerical data that I would like to keep track of. 

Right now I use SUM (manually) each time someone fills out a new form, but I wanted to know if there was a way for the cell to automatically include the newly added rows from the forms? 

Shaine Greenwood

You'll want to count the entire column range of your column, for example:

=SUM([# Club Staff]:[# Club Staff]) 

hquinn

Thank you so much for responding Shaine!

When I entered that formula, it's saying "#CIRCULAR REFERENCE" - is there something else I should have done?

Shaine Greenwood

Hi,

That means that you've got this formula in a column that contains another formula that references its data in some way. (Formulas can't reference one another's data in this way or you'll create a circular reference.)

A couple of ways to potentially combat this: 

1. Move the formula to another column.
2. Reduce the range such that it references cells 1 - x (where x is the bottom of your range).
3. Create hierarchy in your sheet and use a SUM(CHILDREN()) formula instead. More on hierarchy here: https://help.smartsheet.com/articles/504734-hierarchy-indenting-outdenting-rows

On #2, you'll need to make sure that you're adding new rows BELOW row 1, somewhere in the range, or you'll run into the same issue with the range not expanding.

A function's range won't expand when you add new rows above or below it.