parse out totals and show who is in the lead

System

This discussion was created from comments split from: Return value if a cell in the same row has highest value in the column.

SkiPatrolScott

@Dave Stanley I have a FORM that feeds a sheet (PIC1) recording [patroller] [activity] (with much more) I have been able to in my Summary Sheet count the three separate activities, both for daily and cumulative counts (much thanks to @Paul Newcome @Genevieve P. )

In the SHEET I have an [activity count] "=COUNTIFS(Activity:Activity, Activity@row)" that counts all associated Activities, I also have a [helper] "=COUNTIF(Patroller:Patroller, Patroller@row)"that tracks total counts for each patroller for ALL activity. I now have a Summary field #[Number 1]#[Number 2]#[Number 3] that identifies which 3 patrollers are in the lead for most [activities] but this is a cumulative sum of all activities.

=JOIN(DISTINCT(COLLECT(Patroller:Patroller, Helper:Helper, @cell = LARGE(DISTINCT(Helper:Helper), 1))), ", ")

What I would like to do, is parse out the totals for each [activity] based on the [patroller] and then show who is in the lead for each [activity], so that I can display who is collectively in the lead, and who is leading each category (PIC2).

In the pics below, I have whited out the names, but they are there....

Hoping this is possible, so that I can apply the same Fx to other records we are keeping.

Genevieve P.

Hi @SkiPatrolScott

What I would personally do in this instance is set up a second Metric sheet, organized with the Patrollers listed down one column and each of the potential activities listed with them:

Then in the Activity Count column you can use a cross-sheet COUNTIFS like I have in the image above - this counts how many times the Patroller's name appears with each Activity in your source sheet.

Once you have the individual COUNTS, you can use a RANKEQ formula to identify where each patroller falls within that activity category. I used this:

=IF(Activity@row = "", "", RANKEQ([Activity Count]@row, COLLECT([Activity Count]:[Activity Count], Activity:Activity, Activity@row), 0))

The beginning IF statement is just to clear out any of the blue rows I used for headers:

Once you have a Rank applied per-row, you could use a Report to filter and only show you people who have less than 4 in the Rank Column, showing you the top 3 per Activity. You can group the Report by Activity if you'd like!

Cheers,

Genevieve

SkiPatrolScott

@Genevieve P. it took me a minute, but I was able to build this sheet. Turns out our dept will only track Violations so I can just build a single sheet with all names and ranks, I like that its a GOLF rank where 1,2,3,4,4,6,6,6,6,10,10...

I think another faction of MTN safety will track other categories so I may come back to this...