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

✭✭✭✭
edited 10/20/21

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.

Tags:

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

• Employee

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

• ✭✭✭✭

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.

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

• ✭✭✭✭

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.

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

• ✭✭✭✭

Thanks Genevieve this worked 😉

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