Countif -- based on separate row , eliminating certain responses

SkiPatrolScott · 11/28/24

I have a helper row to create a 1st, 2nd, 3rd place in my Summary Tab. But it is counting all entries by participants regardless of the Activity. The Activity selection is a series of drop downs, but I want to eliminate "Training" "Lunch".

The current count formula is

=COUNTIF([Mtn Safety]:[Mtn Safety], [Mtn Safety]@row)

I think if I can eliminate the following formula from counting "Training" and "Lunch" my Ranking Formula will work

=COUNTIFS(Activity:Activity, Activity@row)

SkiPatrolScott · 11/29/24

The more I looked, the Rank Fx does not take into account the [activity], it was only counting entries from [Mtn Safety] using a [Helper]… So I guess it is more complex? Any ideas on how I would modify the formula below to rank the Mtn Safety 'staff' by how many interactions they have 'recorded in the [activity]' all interactions would count except when "training" or "random" was entered.

=JOIN(DISTINCT(COLLECT([Mtn Safety]:[Mtn Safety], Helper:Helper, @cell = LARGE(DISTINCT(Helper:Helper), 1))), ", ")

jmyzk_cloudsmart_jp · 11/29/24

Hi @SkiPatrolScott

The NOT(OR()) condition worked in my demo solution. I created two versions, one summing the Mtn Safety and the other counting.

[SUM] =SUMIFS([Mtn Safety]:[Mtn Safety], Participant:Participant, Participant@row, Activity:Activity, NOT(OR(@cell = "Lunch", @cell = "Random", @cell = "Training")))
[COUNT] =COUNTIFS([Mtn Safety]:[Mtn Safety], ISNUMBER(@cell), Participant:Participant, Participant@row, Activity:Activity, NOT(OR(@cell = "Lunch", @cell = "Random", @cell = "Training")))

https://app.smartsheet.com/b/publish?EQBCT=c7e959b23ea34aaaa896f5a984a8a771

The Sheet Summary field function example;

[No.3 (COUNT] ==JOIN(DISTINCT(COLLECT(Participant:Participant, COUNT:COUNT, LARGE(DISTINCT(COUNT:COUNT), 3))), ", ")

SkiPatrolScott · 11/30/24

Thanks @jmyzk_cloudsmart_jp

I think I may have made it more confusing than I needed too. I think if I just pull the values into a new sheet, I can more quickly see the results, but could use your help on the same string…

the {MSI Violations} is a multiple entry option. So if 'Speeding in a Slow Zone' and 'Jumping in Family Zone' were both listed as Violations, the formula ignores the count. What I would like to see, is a count for every time any violation was checked in the form drop down, I'm pretty sure its a HAS, CONTAINS, INCLUDES prompt?

jmyzk_cloudsmart_jp · 11/30/24

If you are using a "multi-select dropdown", the HAS function should work.

SkiPatrolScott · 12/01/24

I think I got it too work, but now am trying to remember how to have the values ranked. I've used variations of the formulas below in other applications, but can't get it too work here, the third column is where the results should appear.