SUM formula

Jessica Howell

Morning all!

I'm looking for a formula that counts the total score from my team summary, as long as the team name matches

and populate it here, where it will then rank their score in comparison to other teams

I have tried a number of SUM formulas, and cant get one to work for me. Any suggestions?

Thanks in advance!

=Chris Palmer

Hello @Jessica Howell,

The #BLOCKED you're seeing is likely driven because there is a #N/A, or some kind of #ERROR somewhere in your sheet. I would delete the contents in your "Rank by Team Column", then search with Control F for # and see if you can resolve any of those errors, then try your SUM formula again.

Jessica Howell

@Mr. Chris Where it says blocked, that is the ranking. It says blocked because the values are counting incorrectly.

I need this formula for the "Total" column. Sorry for the confusion :)

=Chris Palmer

You'll likely need to write a =SUMIF

This video explains it well:

https://www.youtube.com/watch?v=j7WN4IYZu0I

You can share your existing formula too if you like, it may be helpful.

Jessica Howell

@Mr. Chris Yes, I tried a few SUM/SUMIF formulas, but because its referencing another sheet its not working as per most internet suggestions. Here are a few variations I tried,

=SUMIF({TEAM}, [Team Name]@row, {TOTAL SCORE})

=SUM({TOTAL SCORE}, {Supervisor}, {Supervisor}, [Team Name]@row)

=SUM({TOTAL SCORE}, {Supervisor}, [Team Name]@row)

=SUM({TOTAL SCORE}, IF({TEAM}, [Team Name]@row))

=SUM({Supervisor}, [Team Name]@row) = 0

=SUMIF({Supervisor}, [Team Name]@row) = 0

=Chris Palmer

I tested this on my own Smartsheet and got it to work:

=SUMIF({Sheet 2 Team Name Ranking Column}, Team@row, {Sheet 2 Team Ranking SUM Range Column})

Sheet 2 should reference the Team Ranking Sheet, and the appropriate columns.

The "Team@row" should be the cell in the Team Summary Sheet.

Hope you find this useful.

Jessica Howell

@Mr. Chris Thank you, i have tried this before as well. It comes up as incorrect argument

=SUMIF({team name}, [Team Name]@row, {TEAM})

=Chris Palmer

Shouldn't [Team Name]@row should be Team@row from the Team Summary sheet?

Otherwise you're referencing [Team Name] twice.

Jessica Howell

@Mr. Chris

I changed the heading for the first "team name" it is the team column on the team summary sheet

I need to count all the total scores from this sheet,

As long as the team name is any of the ones on the below list, in the total column