How to Count multiple values within a single cell using COUNTIF Statement


Hi Team,

Have been racking my brain on this one.

Looking to try and count the number of times a specific word is mentioned in a column, however I cannot see to capture the cells where there is multiple words added. .

I am referencing a Raw Data Sheet to a Calculation sheet, to bring back some general figures, but sadly not able to reference the multi word Cells.

Any help would be appreciated :)



Best Answer


  • Mr. Chris
    Mr. Chris ✭✭✭✭✭

    Hello @ProductDan,

    I was able to replicate your scenario and the COUNTIFs formula worked when I removed "&" from the General Issue column.

    =COUNTIF([Drop Down Column]:[Drop Down Column], [General Issue]@row)

  • ProductDan

    @Mr. Chris ,

    Thank you for responding, Sorry this hasn't quite gone to plan for me.

    My Raw Data Sheet is separate to my Calculation Sheet so I am refencing this if it makes much of a difference?

    My Calculation Document is trying to capture the number of times each Issue has been mentioned

    What its not capturing is when a cell has more than one issue

    So in theory if the formula was working correctly I would See Service and RFP numbers a little higher

    The Formula I have tried to far are below, Note I am referencing the Raw data at the beginning:

    =COUNTIFS({2024 Red List Clients General Issue}, "Pricing", "Service")

    =COUNTIFS({2024 Red List Clients General Issue}, "Pricing" OR "Service")

    =COUNTIF({2024 Red List Clients General Issue}, "Pricing" OR "Service, @cell")

    =COUNTIF({2024 Red List Clients General Issue}, OR ("Pricing", @cell), CONTAINS("Service, @cell")))

    All give the same response: #UNPARSEABLE


    Product Dan

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    edited 01/17/24

    Hi Dan,

    If I'm understanding your needs correctly, it might be helpful to simply reference the cell in your calculations

    in [General Issue]@row like so:

    =COUNTIFS({2024 Red List Clients General Issue}, [General Issue]@row)

    I was able to Count "Pricing Service" when found with a total of 3 times.

    I was able to count all these instances on my Calculations Sheet referencing my Testing Sheet.

    You may want to consider copying all data in the Drop Down Column, removing duplicates, then have the remainder be under the "General Issue" column in your calculation sheet. Then you will count every instance.

  • ProductDan
    ProductDan ✭✭
    Answer ✓

    Thanks Mr Chris

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!