Counting Criteria that Overlaps

Alan Donovan

I have a multi select dropdown column where users can select phases of a job they have experience in. For this example, let's say they have the options of "Pre-Execution", "Execution", and "Closeout". I want to total the number of users with "Execution" experience.

Using "=COUNTIF([dropdown]:[dropdown], CONTAINS("Execution", @cell))" includes both "Pre-Execution" and "Execution" in the user count, thus if the user only has "Pre-Execution" experience then they incorrectly get counted. Replacing CONTAINS with HAS then counts users with ONLY "Execution" experience, but if they also have other experience it excludes them completely. Same problem with using a NOT variation as I don't want to exclude those with both "Pre-Execution" and "Execution" experience. Any help on the correct formula to use?

Alan Donovan

After taking a mental break, here's the formula I eventually came up with to solve the issue:

=COUNTIFS(dropdown:dropdown, CONTAINS("Pre-", @cell), dropdown:dropdown, SUM(LEN(@cell) - LEN(SUBSTITUTE(@cell, "Execution", ""))) / LEN("Execution") > 1) + COUNTIFS(dropdown:dropdown, CONTAINS("Execution", @cell), dropdown:dropdown, NOT(CONTAINS("Pre-", @cell)))

Breaking this down, I use SUM(LEN(@cell) - LEN(SUBSTITUTE(@cell, "Execution", ""))) / LEN("Execution") to count how many times "Execution" exists in a cell. Using a COUNTIFS, I count the cell if it has both "Pre-" AND more than one instance of "Execution" (hence the ">1"). This covers the cases where "Pre-Execution" and "Execution" occur simultaneously. In instances where only "Execution" occurs without "Pre-Execution", the second COUNTIFS in the formula counts it by looking for "Execution" without an instance of "Pre-". Hope this helps future users!

Paul Newcome

Try something like this...

=COUNTIF([dropdown]:[dropdown], AND(CONTAINS("Execution", @cell), NOT(CONTAINS("Pre", @cell))))

This will count the cells that do contain "Execution" and do not contain "Pre".

Alan Donovan

Paul,

I appreciate the help. Unfortunately, that excludes the users that have both Pre-Execution and Execution experience so I get an incorrect count of total users that have "Execution" experience.

Paul Newcome

Good point.

Would it be possible to maybe add an "s" onto the end of one of them such as "Executions" or "Pre-Executions" (but not on both)?

Alan Donovan

Yes, I've thought of doing a modification along those lines as well but was seeing if I could avoid it for a smoother user interface (as each employee fills out a form to build the experience database), though it's not that big of a deal so can go that route if needed.

Any idea on how smartsheet's internal column filters/report filters work? There you can select the "Execution" filter checkbox and it works - thinking out loud here.

Also, you won my self bet on if you or Andres would get to my question first - congrats! haha

Paul Newcome

I haven't been able to figure anything else out aside from parsing each selection into its own column.

Although... If they in a multi-select dropdown, then the order would always be the same. I wonder if there might be a way to leverage the delimiter or us a FIND function... I am going to do some testing and revisit this thread.

Hahaha. I am fairly certain @Andrée Starå and I are in different time zones. He is usually on earlier than I am, and I tend to get more of the later posts it seems like.

Paul Newcome

I think I may have a solution using the FIND function, but I need to figure out a specific little detail to be able to accommodate everything... It looks like the FIND function reads your selections in alphabetical order even if they do not appear that way. For example...

In your column properties you list out D, C, B, and A as your options in that order. When a user selects multiple options within the same cell, they are populated in the same order as the column properties. However, using a FIND function to search for "A" will always produce a 1 or a 0. If "A" was not selected, then you will get a 0, but if it was selected (with or without additional selections being made), the FIND function will produce a 1 even though it is displayed last based on your column properties.

All of that is new info to me, so I am going to have to play around with it a little bit to figure out how exactly to make it work. I think it is going to depend on your exact options though, so were you using "Pre-Execution", "Execution", and "Completion" as examples, or are you actually using them in your sheet? Are there any other selections? Can you provide a comprehensive list that includes every selection you plan to make available?

@Andrée Starå @Genevieve P @Mike Wilday @L@123 Did y'all know about the above (Re: FIND function's output when looking at a Multi-Select column)?

Mike Wilday

@Paul Newcome That is really interesting. Thanks for sharing. I will have to play around with that as well!

Alan Donovan

Interesting. You are correct, I was using those three as examples for this issue. In my actual sheet, this item appears in several different columns as it's for different project types. The options also sometimes vary slightly depending on the project type. However, I'm savvy enough with programming languages that if you work it out for the example I'll be able to modify and apply over to my sheet with no problem.

Alan Donovan

After taking a mental break, here's the formula I eventually came up with to solve the issue:

=COUNTIFS(dropdown:dropdown, CONTAINS("Pre-", @cell), dropdown:dropdown, SUM(LEN(@cell) - LEN(SUBSTITUTE(@cell, "Execution", ""))) / LEN("Execution") > 1) + COUNTIFS(dropdown:dropdown, CONTAINS("Execution", @cell), dropdown:dropdown, NOT(CONTAINS("Pre-", @cell)))

Breaking this down, I use SUM(LEN(@cell) - LEN(SUBSTITUTE(@cell, "Execution", ""))) / LEN("Execution") to count how many times "Execution" exists in a cell. Using a COUNTIFS, I count the cell if it has both "Pre-" AND more than one instance of "Execution" (hence the ">1"). This covers the cases where "Pre-Execution" and "Execution" occur simultaneously. In instances where only "Execution" occurs without "Pre-Execution", the second COUNTIFS in the formula counts it by looking for "Execution" without an instance of "Pre-". Hope this helps future users!

Genevieve P.

Paul - that is really interesting. I wasn't aware that FIND read things alphabetically like this, great discovery!

Paul Newcome

@Alan Donovan Nicely done. Glad you were able to get it working, but I think I am still going to play around with the FIND function out of my own curiosity. Haha.