List totals

I have a sheet that lists all of our clients by project. For reporting, i'd like to see a final report or dashboard widget that lists the clients name in order of most mentions within the original list.

For example: List A

Joe Doe

Freddy Mercury

Alice Chains

Eddie Money

Joe Doe

Eddie Money

Joe Doe

Report:

Joe Doe - 3

Eddie Money - 2

Alice Chains - 1

Freddy Mercury - 1

Essentially i'd like a report of how many projects each has.

Is this doable?

Find more posts tagged with

Reports

Marketing

Conditional Formatting

Comments

Troy S.

If there are not going to be duplicate numbers of mentions, a LARGE formula would work.

However, if you need to stack rank and may have duplicates, it gets a little more complex.

You would need are a couple of helper columns, one for a count of mentions and one to figure out if the client has been repeated.

The first one for the count could be a text/number column with the formula

=COUNTIF(Client:Client, Client@row)

This will count how many times a client is mentioned.

For the second one to find the repeat entries, make it a checkbox column.

The formula here will need a way to differentiate between the first unique entry of a client name and the subsequent repeat entries. For this, you would need a COUNTIFS nested within an IF statement, where the COUNTIFS has an absolute reference in the range.

=IF(COUNTIFS($Client$1:Client@row, Client@row) > 1, 1)

This formula will leave the first mention of a client name unchecked and check all subsequent mentions.

You could then pull this into a report, filter on rows where repeat is not checked, and sort by count.

You could also chart directly off the report if you want to create a dynamic chart that will adjust the visual automatically when new clients are added.\

See below for the published sheets!

https://app.smartsheet.com/b/publish?EQBCT=e54a5ce22edf472795c91e41786ddd32

https://app.smartsheet.com/b/publish?EQBCT=012e8a457aa545bfac85988b23282231

-Troy

Jacob H.

Not knowing the layout of your sheet, you could try at least a couple ways:

In the sheet there is a list of the projects. In a column "Number of Projects" there is this formula: =COUNTIFS(Client:Client, =Client1). In front of each client name is the total number of projects. This obviously duplicated with each client name, but that could easily be worked around.

Andrée Starå

Hi Jacob,

I think you’ll have to change or delete your post because you’re showing features that’s not public yet and under NDA.

Best,

Andrée

Jacob H.

Good catch Andrée

stck.mckinney

Thank you, this was vey helpful and worked like a charm.

Andrée Starå

Jacob,

Schhh

Sign in to join the conversation!

Quick Links

Trending discussions

Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…
Why are my answers not showing up when a document is generated?
I have my Smartsheet set to generate a document when a form is filled out. The document is a fillable PDF and when the document generates not all of the answers populate on the generated PDF. I have tried making the PDF again from scratch, I have tried copying and pasting the fields so I know they're the exact same…