Sum all Rows
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.
Answers
-
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
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!