Create a dynamic unique count metric sheet for a leaderboard

Pat Power

Hi All,

I'm looking for a way to create a leaderboard from a sheet that tracks incoming submissions. We want to track the unique visitors and how many submissions each has created over a period of time, and I'm having zero luck.

Any help would be appreciated!

Paul Newcome

Are you able to provide some screenshots (mock data is fine) for context?

Pat Power

Sure thing, see the attached screenshot of the Primary Sheet where the data is first compiled. New entries will be added once we go-live with our form and share it to the org, and we would like to keep track of who is submitting how many as part of a rewards program.

Paul Newcome

My suggestion would be a helper column on the form submission sheet with a COUNTIFS column formula that will put the count on every row.

Then you would create a second sheet that has the numbers 1 through 5 (or however many "top" people you want to pull in) going down a column. From there you could use this to pull in the names:

=INDEX({Form Sheet Name Column}, MATCH(LARGE({Form Sheet Helper Column}, [Number Column]@row), {Form Sheet Helper Column}, 0))

And this to get the counts:

=INDEX({Form Sheet Helper Column}, MATCH([Name Column]@row, {Form Sheet Name Column}, 0))

Pat Power

Hi Paul, thanks for your suggestions!

I'm new to Smartsheet, so not familiar with formulas outside of what is included in some of the templates. Is there good documentaion or an example of how to use the COUNTIF function to count rows like you recommend?

Paul Newcome

@Pat Power You would use the regular syntax for the COUNTIFS function.

=COUNTIFS(first criteria range, first criteria, second criteria range, second criteria)

So something like...

=COUNTIFS([Name Column]:[Name Column], @cell = [Name Column]@row)

pageella

@Paul Newcome Thanks for this! I have managed to use the formula above but have two questions...

1) when the site has the same number it seems to repeat the site name - for example ABE3 and DCA1 both have 84 (3rd place) but its repeating ABE3. Same issue with SDF1 and BNA3

2) I would like to create a leaderboard for different categories.. eg if in Category A, the top 10 are x, y ,z. A full leaderboard if great but I'd like to create a leaderboard for each category so they are 'competitng' with other sites in their category! (makes it more fair!)

Paul Newcome

@pageella If there is a "tie", would you want one to be first and the other to be second, or would you want them shown as both first?

ABC - 10

DEF - 10

GHI - 9

or

ABC & DEF - 10

GHI - 9

pageella

Both first

Paul Newcome

@pageella In that case you would use a JOIN/COLLECT to get the list:

=JOIN(COLLECT({Source Sheet Name Column}, {Source Sheet Helper Column}, @cell = LARGE({Source Sheet Helper Column}, [Number Column]@row)), "delimiter of choice")

Then to pull in your counts you would use:

=INDEX({Source Sheet Helper Column}, MATCH(LEFT([Name Column]@row, FIND("delimiter of choice", [Name Column]@row) - 1), {Source Sheet Name Column}, 0))

pageella

I don't think i am doing it quite right...

where does the index formula go?

Paul Newcome

Replace "delimiter of choice" with whatever you want to use as your delimiter.

"-"

or

" - "

or

" & "

or whatever else you want to use in between.

The INDEX formula goes into another column and pulls in the actual count so that you can see the "score" for 1st, 2nd, 3rd place, etc..

pageella

Almost there...

Paul Newcome

What is the exact formula you have that is throwing the error?

pageella

=JOIN(COLLECT({Source Sheet: Gives Chapter Directory Range 2}, {Source Sheet: Gives Chapter Directory-Activity}, @cell = LARGE({Source Sheet: Gives Chapter Directory-Activity}, Position@row)), "&")

Paul Newcome

It looks like you have that formula in the column that is supposed to be pulling in the counts? If that is the case, it is the wrong formula. To pull in the counts you should use the INDEX formula.