SUM formula

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

  • =Chris Palmer
    =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.

    https://www.linkedin.com/in/zchrispalmer/

  • @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
    =Chris Palmer ✭✭✭✭✭
    edited 05/23/23

    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.

    https://www.linkedin.com/in/zchrispalmer/

  • @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
    =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.

    https://www.linkedin.com/in/zchrispalmer/

  • @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
    =Chris Palmer ✭✭✭✭✭
    edited 05/23/23

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

    Otherwise you're referencing [Team Name] twice.

    https://www.linkedin.com/in/zchrispalmer/

  • @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!