Metrics sheet not counting all values when using a column that allows multiple values per cell

Best Answer

Answers

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭

    I would use a countif/contains formula so it will count each cell that contains what you are looking for.

  • Shonda
    Shonda ✭✭✭✭

    Hi Hollie,

    Thank you for responding. I must be doing something wrong as now I am getting an # Incorrect argument set when i used the following formula. =COUNTIF(CONTAINS({Program/Grouping}, $Category@row))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The HAS function is built specifically for multi-selects. Try something like this:

    =COUNTIFS({Program/Grouping}, HAS(@cell, $Category@row))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Shonda
    Shonda ✭✭✭✭

    Paul,

    Thank you this formula did the trick

  • kerobi
    kerobi
    edited 12/20/22

    This same thing was happening to me. I'm used the HAS formula, but it is not counting rows of data that have more than one asset type listed in the Asset Type column (which is multi-select). In the formula below, I'm am asking the system to count how many case studies have been completed and this formula seems to skip the 6 records that are tagged with more than one asset type in that multiselect "Asset Type" field.

    =COUNTIFS({Status}, "Complete", {Asset Type}, HAS($[Primary Column]@row, @cell), {Content Omit}, "0")

    @Paul Newcome -- when I changed the way I wrote my formula to what you suggested to the other user above, my counts are accurate! I am hoping you can help explain why the placement of @cell or @row makes such a difference? New formula below that actually works! I just don't know why lol, thank you!

    =COUNTIFS({Status}, "Complete", {Asset Type}, HAS(@cell, $[Primary Column]@row), {Content Omit}, "0")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @kerobi The difference is in the HAS function.

    The first section is the range and the second section is the value to search for.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Shonda Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!