List totals

stck.mckinney

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.

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