Countif -- based on separate row , eliminating certain responses
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)
Answers
-
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))), ", ")
-
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")))The Sheet Summary field function example;
[No.3 (COUNT] ==JOIN(DISTINCT(COLLECT(Participant:Participant, COUNT:COUNT, LARGE(DISTINCT(COUNT:COUNT), 3))), ", ")
-
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?
-
If you are using a "multi-select dropdown", the HAS function should work.
-
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.
-
-
yep, I did have to add DISTINCT, as the suits view the ties as 2nd, 3rd.. not 4th, 8th…
=Rankeq([field1]@row, (DISTINCT[field1]:[field1],0))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!