List of Unique Values for Dashboard

Paul Newcome

@Paul Grim You would end up using something along the lines of...

=COUNTIFS(Status:Status, "Pending")

This ca also be used in a Sheet Summary Field. If you are counting from a different sheet, you would just change the range to a cross sheet reference.

Paul Grim

@Paul Newcome Yeah, but then it's not dynamic, because I would need to keep creating a new formula for each new value that was added to the column. I need a solution that can automatically count the unique values in a column without hard-coding the formula with the value I want to count.

Paul Newcome

@Paul Grim I misunderstood what you were trying to do.

You are going to need to use a JOIN/DISTINCT to pull each of the unique values then parse them down a column. Once you have done that you can use cell references instead of hard-coding "specific text" into your formula.

Parsing requires "helper" columns.

Paul Grim

Hmm... it seems strange that you can see these types of summaries in Card view, but you cannot also pull those same summaries into a report or dashboard.

Would the Pivot App provide this functionality? That seems like a much more user-friendly way to go, because I don't think most of our end users are going to be able to consistently pull off a workaround involving multiple helper columns and complicated formulas.

Paul Newcome

From my understanding of how the Pivot App works, it should be a good solution for you, but I have no experience with that add-on at all.

Paul Grim

@Paul Newcome Fair enough. I do have it working using the JOIN/DISTINCT and parsing approach, so I'll see if this sort of thing becomes a regular need and if so I will seriously look into adding the Pivot App to our toolbox.

Many thanks for your help and suggestions along the way!

Paul Newcome

Happy to help! 👍️

If you don't mind my asking... What is your parsing solution? There are a couple of different ones floating around here in the Community, but I am always interested in learning new things.

andrew shelnutt

I began reading this thread to create my own solution. I would be very interested in a pivot table summary solution however i was not aware of that being a thing.

This works fine for me, I intend to automate moving out completed lines in this living table so that the data that stays in the main table is relevant and constantly updated.

this definately takes some time to think of how you want your own data structured for sumarizing and reporting and how that affects the number of supporiting grids, reports and dashboards. Keeping the files clean and categorized in appropriate folder structure is key for not getting lost within all of the formulas and data.

With this solution I have:

1. Main Grid of information with 2 helper columns per unique value summary
2. Stats table that summarizes all unique value by order of current sorting on main table
3. Report to clean up the summary to exclude unwanted information
4. Report with graphs / summaries

andrew shelnutt

After using this for a while I noticed that this breaks very easily by sorting the source table. the functions will not auto change to reflect the row that the functions are on. and I am unable to find a function to tell each row to update its own row number. is there a way to prevent this from breaking by sorting?

Paul Newcome

@andrew shelnutt Take a look at THIS SHEET. It provides a solution for pulling a list of unique values from one sheet and parsing it down a column in another sheet.

Ariel Lasry

Thank you @Paul Newcome this is great

Paul Newcome

Happy to help. 👍️

Cleversheet

Generating a list of unique values in Smartsheet should not be hard to do, nor the guidance for it hard to find. Unfortunately, until now that's not been the case. Here is a solution that I believe will work flawlessly, though of course I welcome critique that will exposes its weaknesses so they can be resolved.

You need three primary elements to pull, in this example, the unique states from a sample list of contacts and display them sorted alphabetically in a report:

• a source sheet (this link is to a public-domain sample contacts listing, and none of the info is believed to refer to real people)
• what I call a uniquer sheet containing this column formula in the [UniqueStates] column, next to the [Uniquer] column that contains sequential numbers starting with zero: =IFERROR(IF(Uniquer@row > 0, (INDEX(DISTINCT({SampleContacts PickState}), Uniquer@row)), IF(Alert@row = "Alert", "+Need more rows in Uniquer", "")), "")
• the resulting report. Any applicable edit in the source data will ultimately appear in the report. Obviously, this report can be included in a dashboard or wherever else useful to you.

Note that cell Alert1 in the Uniquer sheet contains the following formula that must be revised in your case as well: =IF(COUNT(Uniquer:Uniquer) - 1 < COUNT(DISTINCT({SampleContacts PickState})), "Alert", "")

You can play with what you'll find at those links, and clone as needed into your own space. If you name your first (primary) column [Uniquer] and your third column [Alert] then you'll simply need to replace the {bolded items} with your own cross-reference source in both formulas.

I have purposely displayed the error condition to illustrate that you'll be alerted in case of failure to include in your uniquer sheet sufficient sequenced rows to encompass the count of unique conditions you're reporting on. If you edit the Uniquer sheet to remove the error flag, please revert and save before closing your inspection session so the next user will see the error.

The plus-sign in the [UniqueStates] column formula forces the error flag to the top in the report (assuming that column is sorted in ascending order).

yoan.mattos

Hello @Paul Newcome,

I am trying to use your solution above, and I'm getting an error. Also, I tried to look the Example sheet and it is no longer available. Wondering if you can help. I have a table with a column with different values, I want to count the number of times the values occur and display in a dashboard. Basically, what @andrew shelnutt did, but no luck.

Thanks in advance.

Paul Newcome

@yoan.mattos What error are you getting, and can you provide screenshots?