Count with criterion.criteria within multi-select cell

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.

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

    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

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    edited 12/08/20

    This might also work:

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

  • 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

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

  • Genevieve P.
    Genevieve P. Employee Admin

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!