How do I display counts of all of the dropdown options used on one sheet and possibly multiple?

Long story short, I'm building a system to track pending legislation, and for each bill that posts, team members are assigned it to one (or more) categories in a CATEGORY dropdown column. I'm working on a dashboard of high-level views, and I'm trying to figure out the most efficient way to roll-up and display how many (and which, if at all possible) rows are marked with X or Y or Z as their value in CATEGORY.

Suggestions?

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @robinw

    What I would do here is have a separate, Metric sheet set up to gather the data and turn it into values. Down one column, I'd list each Category in individual cells. That way we can reference the "cell to the left" in the formula.

    Then since we're looking into a multi-select column, we'll use the HAS function to see if that column has that one selection among others, like so:

    =COUNTIFS({Category Column}, HAS(@cell, Category@row))

    Let me know if that makes sense and works for your purposes! If not, it would be helpful to see screen capture examples of how your source sheet is set up.

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!