parse out totals and show who is in the lead


  • @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.
    Genevieve P. Employee Admin

    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!



  • @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...

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!