Will there be a way to sort Grouped Reports based on the count of the grouping?

Jake Gustafson

I really love the functionality to group data in a report. Additionally, I am excited when that display is supported within Dashboards. However, I'm curious if there will be the option to sort the report data based on the count of the grouping rather than a specific column? I'm treating things more like a pivot or pareto, where I want to see the high frequency items to act on them accordingly, but I don't want to have to scroll through a long list of items to find those high occurring items.

I have attached a screen shot of a basic example that I have. I'd like to sort descending (or high to low) on the count of the Assigned To field.

Smartsheet Report Grouping Sort 20210325.jpg

Genevieve P.

Hi @Jake Gustafson

That's a good suggestion! Please let our Product Team know about your feedback by filling in this form, here.

Since Sorting by the summary values isn't currently supported in a Report, the way I would organize this data is by adding in a helper column to the source sheet. You can set up a simple COUNTIF formula like so:

=COUNTIF([Assigned To]:[Assigned To], [Assigned To]@row)

This will automatically populate the same COUNT number summary that you see in the Report and associate it with each row. Then you can add this helper column into your Report, hide the column, and use the values in this column as the first criteria to Sort your values. Will this work for you?

Cheers!

Genevieve

Jake Gustafson

Thanks @Genevieve P. I submitted the Product Enhancement Request form. I tried the formula you provided, it sort of works. I want/need the Assigned To as my visual queue to avoid seeing the large number, then have to open the group to see who is in there. It's a step in the right direction though.

Also learned a valuable lesson in overwriting some data in the wrong column trying put in a column level formula. Not fun there. 😫

Genevieve P.

Hi @Jake Gustafson

Oh no! You can revert changes in a column as long as you haven't Saved the sheet. Or can you use the cell history to retrieve the data if it was saved?

Would you be able to clarify what you want to see in the Report? Do you want it to look the same as your image above, but just re-ordered?

- Genevieve

Jake Gustafson

I had already saved the sheet, but I was able to get the cell data to populate what had been there in around 60 records, so not horrible.

Yes, I am looking for it to look exactly the same as my screen shot from above, but ordered differently. I tried to summarize the new column that I added with your formula, then thought I could sort the Grouped Report by the Count of the Assigned To column, but I believe it is sorting the individual rows within the group based on that criteria and not the groups themselves.

If you've got more ideas, then I'm open to it. The biggest piece will be having the grouped report display available in a Dashboard, so that a lot of this data can be served up to the team in charge of the data to see what trends are there and take necessary action.

mabo60

This does not seem to work with the filtering. If a row is filtered out of the report the count from the unfiltered data used in the command.

This makes the command calculated count in the report not accurate to the actual data in the report.

I have a column called "Owner" and I used "=COUNTIF(Owner:Owner, Owner@row)".

Todd101984

Was there any action taken to allow Option to sort report based on the count of the grouping? That's an Enhancement I can use instead of adding another column (I already have 30+ columns to manage my source file).