List totals

stck.mckinney · 02/01/19

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?

Troy S. · 02/01/19

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. · 02/01/19

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å · 02/01/19

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. · 02/01/19

Good catch Andrée

stck.mckinney · 02/12/19

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

Andrée Starå · 02/12/19

Jacob,

Schhh

List totals

Comments

Categories