Count with criterion.criteria within multi-select cell

Amr Ismail
Amr Ismail ✭✭
in Formulas and Functions

I have multi-select cells in a sheet. I can use countm to count all selected items in a specific cell. However I need to count specific selections based on criteria. For example: A dropdown contains:

L1-ABC, L1-LMN, L1-XYZ,L2-ABC,L2-LMN,L2-XYZ, L3-ABC,L3-LMN,L3-XYZ

One cell has: L1-ABC,L2-XYZ,L2-ABC,L3-XYZ

I want to search how many times a selection started with L2 was made? So the formula should return 2 for the above cell.

· Share on FacebookShare on Twitter

Best Answer

Answers

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    @Amr Ismail

    I have a solution for you but it isn't terribly pretty... I can't find a way of nesting a LEFT type function inside a HAS function to only look at the first 2 letters of each code. I also can't find any wildcards that work with HAS. So I have achieved your requirement through a slightly long winded fashion, but it does work! :D

    image.png

    My Answer cell (returning 7) is looking at the column COUNT MULTI and returning all those that begin with L2.

    My formula is:

    =SUM(COUNTIF([COUNT MULTI]:[COUNT MULTI], HAS(@cell, "L2-ABC")), COUNTIF([COUNT MULTI]:[COUNT MULTI], HAS(@cell, "L2-LMN")), COUNTIF([COUNT MULTI]:[COUNT MULTI], HAS(@cell, "L2-XYZ")))

    I understand that it is fine when there are only 3 or 4 values in the list that meet the condition you want, but as it is a multi-select I would imagine the list of entries is kept relatively small (?) so this would/could work for you?

    I hope it gives you something to work on.

    Kind regards

    Debbie

    · Share on FacebookShare on Twitter
  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    edited 12/08/20

    This might also work:

    =COUNTIFS(Count Multi]:[Count Multi], FIND("L2", @cell) > 0)

    · Share on FacebookShare on Twitter
  • Amr Ismail
    Amr Ismail ✭✭
    https://community.smartsheet.com/discussion/comment/271182#Comment_271182

    Thanks Debbie. Your answer worked. Thank you. It works if the options to search from are limited. I hope later Smartsheet adds criteria to CountM function

    · Share on FacebookShare on Twitter
  • Amy Koné
    Amy Koné ✭

    Hi there. Glad to have found this thread and would love a little help tweaking for a specific need. I have columns where cells contain up to several responses (multi-select). I need a formula for my sheet summary to count responses. For example, how many times has someone selected Urban (whether by itself of with other value/s). My formula is only counting when Urban appears by itself.

    Population Density (column title)

    Values (multi-select): Urban, Rural, Suburban, Tribal, Other

    Appreciate any help. Formulas aren't my forte (yet).

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    Hi @Amy Koné

    To count within a Multi-Select column you'll want to use the HAS function (see here).

    For example:

    =COUNTIF([Population Density]:[Population Density], HAS(@cell, "Urban"))


    This will check each cell (using @cell) in the previously stated range to see if it HAS that text, either on its own or with other values. Keep in mind that HAS is specific to exactly the text specified, so "Urban" will only count for "Urban" and it won't count "Suburban" even though the text is contained in that word.

    Let me know if this works for you!

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!