CountIfs & Contains - not multiselect

I have searched all over the Community for something like this but the only thing I am seeing are solutions around multiselect and the HAS( ). In my case I have a second sheet, Product Details that has a Text/Number, POM Child ID column that can contain multiple values, e.g. 251, 252.

This is my attempt that is coming up with 0s.

=COUNTIFS({Product Details POM Child ID}, CONTAINS(@cell, [Parent ID]@row), {Product Details Matl Status}, "PP")

I have a similar formula in another column to count the NP status. Just in this image I would expect...

250 1-PP, 0-NP

251 3-PP, 4-NP

252 3-PP, 0-NP

Any help is greatly appreciated.

Paul

Best Answer

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Paul Reeves

    Try this.

    =COUNTIFS({Product Details POM Child ID}, FIND([Parent ID]@row,@cell)>0, {Product Details Matl Status}, "PP")

    Will this work for you?

    Kelly

  • Paul Reeves
    Paul Reeves ✭✭✭✭✭

    Thank you. That makes all the sense in the world. I was so stuck on CONTAINS and HAS I never considered FIND.

    A side question, what is the main benefit of @cell vs @row? I have not been able to find good explanation as to when to use either one. I tend to start with @row and then try @cell but never really understanding what is the difference.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!