Countifs on a column that is a multi-select drop down..

I am trying to do a countif and one of the fields is a multi-select drop down and isn't returning the correct value, no idea what to do

R4+ BP007 Change Inventory Range 1 is the field that is the multi-select column.

=COUNTIFS({Status}, =[01 Open]$1, {Primary Function}, =$[Primary Column]5, {Fit/Gap}, ="Gap (Core Process)") + COUNTIFS({Status}, =[01 Open]$1, {Primary Function}, =$[Primary Column]5, {Fit/Gap}, ="Gap (Local Process)") + COUNTIFS({Status}, =[01 Open]$1, {Primary Function}, =$[Primary Column]5, {Fit/Gap}, ="Gap (Non-functional)", {R4+ BP007 Change Inventory Range 1}, $[01 Open]$10)

Tags:

Best Answer

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

    I personally am most curious about that third range - {Fit/Gap}


    It looks like that is the only thing changing across each of the different COUNTIFS aside from the last one containing {Range 1} and the HAS function.


    You also need to use "@cell" inside of the HAS function instead of duplicating the range.

    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

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try this:

    =COUNTIFS({Status}, =[01 Open]$1, {Primary Function}, =$[Primary Column]5, {Fit/Gap}, ="Gap (Core Process)") + COUNTIFS({Status}, =[01 Open]$1, {Primary Function}, =$[Primary Column]5, {Fit/Gap}, ="Gap (Local Process)") + COUNTIFS({Status}, =[01 Open]$1, {Primary Function}, =$[Primary Column]5, {Fit/Gap}, ="Gap (Non-functional)", {R4+ BP007 Change Inventory Range 1}, HAS(@cell, $[01 Open]$10))

    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

  • Hi @JTowler

    I see you marked Paul's answer as not resolving your question. Can you clarify what happens when you add in the HAS function with @cell, do you receive an error or an incorrect result? If you receive an error, which one is it? Can you post screen captures (but block out sensitive data)?

    Cheers,

    Genevieve

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

  • Hi both,

    Thanks for your responses - I've added in the formula as above but it's bringing back the incorrect count, this is the one formula I can't seem to get my head around - there aren't any errors. The total is coming back as 131 however the total should be 81 🤔



  • Hi @JTowler

    Can you clarify what each of your four ranges are? It would be helpful to see a screen capture of each of these columns, noting what type of column they are:

    {Status}

    {Primary Function}

    {Fit/Gap}

    {R4+ BP007 Change Inventory Range 1}


    Is it possible that one of your three statements is creating duplicate Counts?

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

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

    I personally am most curious about that third range - {Fit/Gap}


    It looks like that is the only thing changing across each of the different COUNTIFS aside from the last one containing {Range 1} and the HAS function.


    You also need to use "@cell" inside of the HAS function instead of duplicating the range.

    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

  • Amazing - thanks guys!

    I simplified the formula and added in @CELL (What does this actually do?) and seems to work now!!


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The "@cell" basically tells the formula to evaluate the previously established range on a cell by cell basis.

    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!