SUM formula

Options

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!

Answers

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    Options

    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
    Options

    @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 :)

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    edited 05/23/23
    Options

    You'll likely need to write a =SUMIF

    This video explains it well:

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

  • Jessica Howell
    Options

    @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

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    Options

    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
    Options

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

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

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    edited 05/23/23
    Options

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

    Otherwise you're referencing [Team Name] twice.

  • Jessica Howell
    Options

    @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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!