I need to SUM points based on user and then sort by aggregated points

Tsega Thompson
Tsega Thompson ✭✭✭✭
edited 10/20/21 in Formulas and Functions

I have a sheet similar to the one below:

I need a way to display my results like this:


I can create a report but it won't sort it based on the aggregate function.

Any help would be much appreciated.

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    @Tsega Thompson

    No problem! What have you tried?

    A cross-sheet SUMIF works like this:

    =SUMIF({Column To Filter By}, "Filter Criteria", {Column to Sum})


    So in your instance, something like this:

    =SUMIF({Column With User}, "User Name", {Column with Totals})


    You could also have the User Name in a cell in the current sheet (like in your second image) and then reference this instead of the quotes, like so:

    =SUMIF({Column With User}, User@row, {Column with Totals})


    If this hasn't helped, it would be useful to see screen captures of your actual Smartsheet with the formula in a cell (but please block out sensitive data).

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Hey @Tsega Thompson,

    Reports have a Summarize and Grouping Feature where we can display this data in a similar fashion. I've created the example below to demonstrate this:

    I used the Group Feature to Group the Report by User then used the Summary Feature to Sum the Points:

    If you find that the User Column within the Report does not display in alphabetical order, you can create an additional Column within your Sheet that assigns a Numerical Value according to the User and Sort the Report by this Column (use a Formula if necessary):

    I hope this helps!

    Jaykel

  • Tsega Thompson
    Tsega Thompson ✭✭✭✭

    Hi Jay,

    I mentioned the sorting issue when I go through reports. If you look at your results you will see that it is still sorted by user, despite using the sum to sort.

    User b & d both have an aggregated score of 75 but they do not fall next to each other.

  • Hi @Tsega Thompson

    Thank you for clarifying! You are correct, Grouping will sort by the Grouped Column alphabetically (either ascending or descending).

    In your instance, I would suggest creating a Metric Sheet with cross-sheet SUMIF formulas and then Sort this Sheet based on the total points, using this as your source in the Dashboard.

    Here's information on formulas:

    Let me know if you need help building out the formula and I'd be happy to clarify further.

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Tsega Thompson
    Tsega Thompson ✭✭✭✭

    Sumif was my first thought, but I don't seem to be doing it right. I kept getting "unparseable". Any help would be most appreciated.

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    @Tsega Thompson

    No problem! What have you tried?

    A cross-sheet SUMIF works like this:

    =SUMIF({Column To Filter By}, "Filter Criteria", {Column to Sum})


    So in your instance, something like this:

    =SUMIF({Column With User}, "User Name", {Column with Totals})


    You could also have the User Name in a cell in the current sheet (like in your second image) and then reference this instead of the quotes, like so:

    =SUMIF({Column With User}, User@row, {Column with Totals})


    If this hasn't helped, it would be useful to see screen captures of your actual Smartsheet with the formula in a cell (but please block out sensitive data).

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Tsega Thompson
    Tsega Thompson ✭✭✭✭

    Thanks Genevieve this worked 😉

    =SUMIF({Column With User}, User@row, {Column with Totals})

  • Wonderful! Thanks for letting me know. 🙂

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!