Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Counting occurrences of values in a multi-select dropdown list

I have looked through all of the answers for this topic, and still cannot find a solution that works for me. I have a sheet with a multi-select dropdown used to code responses. In another sheet, I want to count the number of times a certain value appears in any dropdown in the sheet column. Here is my formula:

=COUNTIFS({All Action Items Coding}, CONTAINS("Accountability", @cell))

{All Action Items Coding} is a column range in another sheet that contains the dropdown answers. I want to see if any cell in {All Action Items Coding} contains the string "Accountability". I have created the function based on answers to similar previous questions, but I get "#CONTACT EXPECTED" as an error.

Still don't understand what I am doing wrong…

Thanks so much,

Emily

Best Answer

  • Community Champion
    Answer ✓

    @emilyc9 I tested this on my end and your formula is ALMOST correct. Your "=countifs( " should just be a "countif("… no "s". You're only doing one if which is the contains.

    Also if your getting a "#Contact Expected" error, it's because the column type where your formula is at is set to a contact column which cannot calculate a non contact value. Because it's a contact column it's literally expecting contact… not a count.

    Matt Lynn

    Community Champion

    Archer Consulting Team

Answers

  • Community Champion
    Answer ✓

    @emilyc9 I tested this on my end and your formula is ALMOST correct. Your "=countifs( " should just be a "countif("… no "s". You're only doing one if which is the contains.

    Also if your getting a "#Contact Expected" error, it's because the column type where your formula is at is set to a contact column which cannot calculate a non contact value. Because it's a contact column it's literally expecting contact… not a count.

    Matt Lynn

    Community Champion

    Archer Consulting Team

  • ✭✭

    Thank you immensely. This was the answer. I did have the cell type set to "Contact List"!!!!

    Really appreciate it!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions