Counting items from a multiple drop down list

I have a list of action items that can be assigned to several departments by using a multiple drop down list with dept. names in the assigned column

I am trying to create a summary sheet to count the number assigned

I have tried both Contains & HAS

But my answer is 0 - Just using the count if gets me 23 and there are 2 where multiple depts are chosen including OPS so I should have 25.

Using HAS

Once again my answer is 0

The {LL Action Tracker Range 1} is my assigned row in my main sheet

Thank you!

Best Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    edited 03/04/24 Answer ✓

    @Amy A

    Try This...

    =COUNTIF({LL Action Tracker Range 1}, HAS(@cell, Section@row))

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Amy A

    Try this:

    =COUNTIFS({LL Action Tracker Range 6}, <>"", {LL Action Tracker Range 1}, HAS(@cell, Section@row))


    With a COUNTIFS you'll always want to list the {column} first, then your criteria. The HAS is the criteria, which is why we list is after the {range}. Let us know if this makes sense!

    Cheers,

    Genevieve

Answers

  • JamesB
    JamesB ✭✭✭✭✭✭
    edited 03/04/24 Answer ✓

    @Amy A

    Try This...

    =COUNTIF({LL Action Tracker Range 1}, HAS(@cell, Section@row))

  • Amy A
    Amy A ✭✭

    Thank you very much!!

  • Amy A
    Amy A ✭✭

    I was hoping by helping me with this item, I could figure out my countifs but no such luck.

    I am trying to also then identify these counts on my summary as completed & open by priority.

    This above has worked for my countif

    This has worked for my completed: =COUNTIFS({LL Action Tracker Range 6}, <>"", {LL Action Tracker Range 1}, Section@row) - but does not include those with multiple dropdown - I have tried the HAS & CONTAINS formula with no luck

    Is NOT working:

    =COUNTIFS({LL Action Tracker Range 6}, <>"", CONTAINS(Section@row, {LL Action Tracker Range 1}))

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Amy A

    Try this:

    =COUNTIFS({LL Action Tracker Range 6}, <>"", {LL Action Tracker Range 1}, HAS(@cell, Section@row))


    With a COUNTIFS you'll always want to list the {column} first, then your criteria. The HAS is the criteria, which is why we list is after the {range}. Let us know if this makes sense!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!