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

  • Matt Lynn ACT
    Matt Lynn ACT 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

  • Matt Lynn ACT
    Matt Lynn ACT 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

  • emilyc9
    emilyc9 ✭✭

    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!