Identifying all unique values for a Column, then counting number of rows with that value

Robin MacRorie

I googled around for a while, but haven't seen this scenario (maybe I just missed it). I have data on a Master Sheet. A subset of that data is on the Backlog Sorting Report.

I want to somehow see a list of all of the values for a certain column and then a total number of rows that match that value. I don't want to write a formula for each possible column value because those values may change.

Once I have this automated list of each value plus the number count for that value, I want that to be included in a chart on a dashboard. Ideally, I would be able to click on a chart field and go back to the Backlog Sorting Report, but now filtered to that Topic. (I don't want much, I know....)

Even if someone could direct me to a help article for individual pieces, that would be very helpful. I've seen things on getting the number count, but not on generating the rows for each new value

Robin MacRorie

In case someone else comes looking, here's what I was finally able to put together.

Add three columns to the master sheet. ID the Topics, List topics and Count Values.

Formula for the ID the Topics column (starting with row 2; row 1 was locked for formatting, but I don't think it's needed anymore. but I was also tired of messing with it): =IF(COUNT(DISTINCT(Topic$1:Topic@row)) <> COUNT(DISTINCT(Topic$1:Topic1)), COUNT(DISTINCT(Topic$1:Topic@row))) - 1

Formula for the List topics column: =IF([ID the Topics]2 > 0, Topic2)

Formula for the Count Values column: =IF([ID the Topics]@row > 0, (COUNTIFS(Topic:Topic, Topic2)))

Copy/paste those formulas down the appropriate columns. Another day I'll look at adding an alert for if there are columns without the formula in them.

This does work and I was able to get the chart widget in to show the data I wanted now.

James Keuning

I would be able to click on a chart field and go back to the Backlog Sorting Report, but now filtered to that Topic.

ME TOO!

But sir, this is Smartsheet. This is not Tableau. (That's me trying to be funny)

The Dashboard/Report/Chart functionality is limited. Even if we imagined building an individual report for each color or category, which takes the user to pre-filtered data based on what they click, that would be something that we can't do.

Clicking behavior is as follows, and it operates at the chart level.

Robin MacRorie

@James Keuning For the chart piece - thanks. I was hoping I overlooked something, but not terribly surprised. I'll just open the report and sort.

Still stuck on the other pieces.

Robin MacRorie

In case someone else comes looking, here's what I was finally able to put together.

Add three columns to the master sheet. ID the Topics, List topics and Count Values.

Formula for the ID the Topics column (starting with row 2; row 1 was locked for formatting, but I don't think it's needed anymore. but I was also tired of messing with it): =IF(COUNT(DISTINCT(Topic$1:Topic@row)) <> COUNT(DISTINCT(Topic$1:Topic1)), COUNT(DISTINCT(Topic$1:Topic@row))) - 1

Formula for the List topics column: =IF([ID the Topics]2 > 0, Topic2)

Formula for the Count Values column: =IF([ID the Topics]@row > 0, (COUNTIFS(Topic:Topic, Topic2)))

Copy/paste those formulas down the appropriate columns. Another day I'll look at adding an alert for if there are columns without the formula in them.

This does work and I was able to get the chart widget in to show the data I wanted now.

James Keuning

So you can click on your chart and get taken to your sheet, with the value filtered to the topic?

Robin MacRorie

@James Keuning

Clicking on the chart and getting taken to the filtered topic was the icing on the cake in that ask. I had been hoping for help building the formulae to get the data first. The chart portion would have been nice, but the formulae were what I really needed most.

So, no. As you said, the chart bit was a bridge too far.