Sum all Rows

Ramsay Zaki

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.

Jose da Silva

You can select the entire column by typing the column name between straight bracets, just not entirely sure if it will work on your formula

Andrée Starå

Hi Ramsay,

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

 

Jim Schaffhausen

I would recommend doing the summary in a new column. Then sum the whole rows -

(Team:Team) (Amount:Amount)

Another option is to use the sheet summary - then visualize the data in a dashboard via a summary report or use 3 metric widgets.