Trying to use COUNTIFS to count Items in a drop-down column

Options

I have a drop-down column for Issues and a column for Campuses, and I'm trying to count the number of times an issue shows up for each campus. The formula that I'm using is counting the issues, but only if it is the only item selected in the drop-down:

=COUNTIFS({Campus}, "Chico", {Issue}, "Unilateral Change")

Instead of retuning a count of 4, its counting only 3.

Best Answers

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @NewtoSmartsheet

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.


    Or if you like me to fix the formula directly on your sheet please share me as an admin on a sample copy of your sheets ( Source & Destination ) and i will Create the exact formula for you then you can copy it to your original sheet.


    My Email for sharing : Bassam.k@mobilproject.it

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @NewtoSmartsheet

    If you're Counting in a MultiSelect column, you'll want to use the HAS function to see if the cell has that value along with others or not.

    Try this:

    =COUNTIFS({Campus}, "Chico", {Issue}, HAS(@cell, "Unilateral Change"))

    Cheers!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • NewtoSmartsheet
    Options

    Hi Genevieve,

    It still will not count anything that has more than one item in the multi-dropdown field.

  • Genevieve P.
    Options

    Hi @NewtoSmartsheet

    Can you clarify what each of your column references are? Is the  {Issue} field the multi-select or is the {Campus}? Or both?

    It would be helpful to see a screen capture of the source sheet, but please block out sensitive data.

    Thanks!

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • NewtoSmartsheet
    NewtoSmartsheet ✭✭✭
    Answer ✓
    Options

    Genevieve,

    It worked! I had forgot a comma in the equation. Thank you.

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    I'm glad to hear it! No problem at all.

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • NewtoSmartsheet
    Options

    one more question: I am trying to count all of the times that "Unilateral Change" is selected for all of the campuses, the formula is not counting the instances where the campus multi-dropdown has more than one campus selected. This is the formula I tried:

    =COUNTIFS({Campus}, HAS(@cell, [Metric Description]13:[Metric Description]37), {Issue}, HAS(@cell, "Unilateral Change"))

  • Genevieve P.
    Options

    Hi @NewtoSmartsheet

    Can you clarify what this range is:

    [Metric Description]13:[Metric Description]37


    Are you looking for multiple possible Campuses?

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!