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)

Find more posts tagged with

Accepted answers

All comments

SkiPatrolScott

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

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

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

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

SkiPatrolScott

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.

jmyzk_cloudsmart_jp

You can use the RANKEQ function. @SkiPatrolScott

=RANKEQ(Count@row, Count:Count, 0)

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

SkiPatrolScott

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))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.