UNPARSEABLE error with COUNTIF and MultiSelect

This discussion was created from comments split from: COUNTIF and Multi-Select.

Answers

  • Kara,

    When I try using this formula I keep getting #unparaseable

    =COUNTIF({Dunder Mifflin - Workday Area Concerns}, CONTAINS(Category21@row,@cell))

    My formula does the following

    {Dunder Mifflin - Workday Area Concerns} = The Range I am looking to count off of

    Category21 = The field I want the have returned. In this example that field is "FUNC - Core HCM"


    I got my formula to work with the following formula

    =COUNTIFS({Dunder Mifflin - Workday Area Concerns}, CONTAINS(Category23, @cell))

    The issue here is the count is wrong. As it won't return all of the values. Example I know I have a count of 6 "FUNC - Core HCM", but it is only returning 4.

    Any help you could give would be very helpful.

  • Alejandra
    Alejandra Employee

    Hi @Keith Barres,

    The #UNPARSEABLE error is being returned because you're already referencing row 21 of the Category column, but you've also appended @row after the row number (@row should replace the row number). This formula should fix the #UNPARSEABLE error:

    =COUNTIF({Dunder Mifflin - Workday Area Concerns}, CONTAINS(Category@row,@cell))

    As for the formula returning an incorrect number for a specific category, it would be helpful if you could provide screenshots so we can see what's going on. You could also reach out to our Support team and they can troubleshoot with you. 

  • Alejandra
    Alejandra Employee

    @Keith Barres You might also want to consider using HAS instead of CONTAINS. HAS looks for an exact match. If needed, more information on the HAS function can be found in our help article: https://help.smartsheet.com/function/has

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If it is working but not counting all of the cells even with the CONTAINS function, you may want to start by double checking your source data to ensure it matches. While the CONTAINS function is not case sensitive, if a cell in the source data has an extra space or a minor typo, it will not be picked up/counted.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!