Sum all Rows

Ramsay Zaki
Ramsay Zaki ✭✭✭✭✭✭
edited 12/15/19 in Formulas and Functions

My sheet has 3 columns (Team, Activity, Amount). I have a summary section at the top of my sheet where I want to use a SUMIF function to total up the amount for each Team.

Generally, I can get the SUMIF function to work just fine. But, I run into an issue when adding new rows.

The function looks like this: 

=SUMIF(Team10:Team50, "Team 2", Amount10:Amount50)

=SUMIF(Team10:Team50, "Team 3", Amount10:Amount50)

=SUMIF(Team10:Team50, "Team 3", Amount10:Amount50)

When I add a new row (Row 51)... the formula doesn't automatically pick up this new row.

How can I make the formula always grab the last row?

Alternatively, I tried removing the row #s and just referencing the entire column like this: =SUMIF(Team:Team, "Team 1", Amount:Amount). But, then this causes a #CircularReference error. With this approach, I can get it to work if I put the formula in its own separate column from the ones being referenced... but then that looks odd to not have the total in the actual Amount column.

I know I can use the new Sheet Summary section to do this... but I am trying to display this information in a Chart Widget on a Dashboard... and those cannot reference Sheet Summary data.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!