CountIfs & Contains - not multiselect

Paul Reeves

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

Kelly Moore

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

Kelly Moore

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

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.