Count Multi Select dropdown values

Good evening everyone! I’m looking for some help with a formula for Multi Select drop down. I’m looking to reference another sheet and count or sum the number of values selected in a drop down multi list regardless of what has been selected.

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Noble B

    Are you looking to Count the number of values for a specific row (so, per-cell), or are you just wanting to count all of the selections throughout the entire column?

    The COUNTM function will count the number of elements in a multi-contact or multi-select dropdown column cell or cell range. It returns the total number of elements found, so if you're referencing the entire column and cell 1 has "Apples" and cell 2 has "Apples" and "Oranges", it would count three. Is this what you're looking to do?

    If this doesn't work for you, it would be helpful to see a screen capture of this sheet with the drop-down and an explanation of exactly the numbers you're looking to retrieve (but please block out any sensitive data from the screen capture!)

    Cheers,

    Genevieve

  • Noble B
    Noble B ✭✭

    Yes @Genevieve P that's exactly what I'm looking for. I'm also looking to count the number of element selected within a specific timeframe while referencing another sheet. The bellow screenshot is what i've been using however, it's returning the same number for all months which i not correct.


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Noble B

    COUNTM can only look at one range. If you're wanting to add criteria to narrow down that {type} range, you can use the COLLECT function to only collect rows that match the other criteria and present that as the initial range.

    Try something like this:

    =COUNTM(COLLECT({Type}, {Date}, >=Month@row, {Date}, < Month@row))

    Let me know if this works for you!

    Cheers,

    Genevieve

  • Noble B
    Noble B ✭✭
    edited 11/05/20

    @Genevieve P thank you so much, that worked beautifully!

  • Genevieve P.
    Genevieve P. Employee Admin

    No problem! I'm glad we could get it working for you. The Collect function is one of my favourites. 🙂

  • sara au
    sara au ✭✭

    @Genevieve P. this is very helpful! I'm looking to do something similar but having a hard time amending your formula into mine. Looking to count the Type of Project to report out by month from Created dates. I have to repeat the count for another column too, but could amend that myself I think. And then separately, I need to track the average time of fulfillment per month by comparing Created date with the date Done was checked (if that's possible).


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @sara au

    What I would do here is have a helper column that simple returns the Count for each row:

    =COUNTM([Type of Project]@row)

    That way you have a number associated with the row. You could use a Report to filter by the Created Date and use the Summary function to SUM all the values in your helper count column. Is this what you're looking to do?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!