COUNTIF substring or wild cards

Rohit Rao

I have a column of Part Numbers. Each cell in the column may have a single part number or  comma separated part numbers. I want to count the number of occurrences of a particular part number in the column. I searched the help and community pages but wasn't sure what's the latest. Is it possible to use wild cards or any other alternatives in COUNTIF()? One work around would be to have a column each for the part numbers and then FIND() the part number in each row and then COUNT on that column. But we have thousands of part numbers :-(

Matt Hines

You should be able to do this:

 

=COUNTIF([Part Numbers]1:[Part Numbers]10, FIND("123456", @cell) > 0)

 

Where 123456 is the partnumber you are looking for. Note the "> 0" is because if the FIND() result is no partnumber it will result 0 so anything above that will include that part number.

 

Good luck!

Rohit Rao

Thanks Matt! this is perfect. @cell is quite powerful. 

 

Rohit