I need help with creating a formula using Countif or Countifs.


I am trying to pull data from one range that has mulitiple values. Does anyone know how to write a formula to not pull information from more than one range?

I am using the formula =countif(sheet reference data), (question ie: What do you enjoy most about your holiday party? @row.

This works for when the question isn't set up with multiple values. However I have the question from the picture that does have multiple values. How do I get it do count for example Dancing if it has the other values.

Can someone lead me in the right direction?


Best Answer


  • Eric Law
    Eric Law ✭✭✭✭✭✭

    @Alex Price you can use the contains function.

    =COUNTIF([What do you enjoy most about your holiday party?]:[What do you enjoy most about your holiday party?], CONTAINS( "Dancing", @cell))

    You just need to change the Dancing to whatever you want to count.

  • Alex Price

    I think that this is getting me closer. However it is still giving me issues.

    I am currenting using a sheet to compile all these equations in.

    When I type in the formula, I am referencing another sheet that contains the raw data. How do I get it to count the values when I am referencing from the raw data sheet (AC Holiday Party Survey)?

    When I have a simple question, yes or no, it will work.

    Sorry I am very new to Smartsheet.

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓

    For multi-dropdowns, use HAS with @cell instead:

    =COUNTIF({AC Holiday Party Survey Range 1}, HAS(@cell, "DJ/Music"))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!