CountIf formula issue with similar multi-dropdown items in cells


I am trying to count how many items in a multi-select drop down column fit the criteria to accurately return the number. My main issues are that the multi select columns have multiple criteria that need to be counted and that I have location names that area similar.

Ex: TL1-6, TL1-60, TL1-61, ....TL1-69

I have tried:

=COUNTIF({Range}, CONTAINS("TL1-6", @cell))

then to remove the other items for TL1-60 through TL1-69 I added "-SUM({Range})" to include the formula cells of counts for TL1-60 through TL1-69, but the formula for TL1-6 only has each cell that fits the criteria count as 1 rather than counting multiple individual items if a cell has both TL1-6, TL1-60 as separate dropdown items in one cell. This causes the number to return to be 0 rather than counting every item that fits the criteria in the range and being able to subtract any of the distinct TL1-60 through TL1-69 items and giving me the correct count.

I have tried testing out various ways to use the CountM, Find, and Has functions with no success yet, so any guidance on this would be very much appreciated!


  • Courtney S.
    Courtney S. ✭✭✭✭✭

    This seems to be something that would work better with the HAS function, since that is specifically a function to find an exact match to a multi-select dropdown answer. What did your try with HAS look like, and what wasn't working? HAS Function | Smartsheet Learning Center

    Also, since you have the dropdown option to count listed in that first column, you could have the COUNTIF function look at the cell in that column instead of typing out the criterion in the formula for every row.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!