How to "countif" for cells that contain a similar value

HappyDays

I have a column with a list of Company Names in free text, and I would like to count all of the cells with the same name, though they may be typed slightly different. Such as, missing "Inc." or "Assoc.", etc.

I have a separate sheet with a column of the correct names. I would like to display the quantity next to this column. I use the COUNTIF formula and I return a number; however, it is not the correct total since some of the names vary slightly.

KPH

Hi

You can use a CONTAINS function to return the count for those that contain a certain string. How well this works will depend on your data and if you can identify the correct company based on part of the name without mis-identifying.

Here is how it would work.

If your free text looks like this:

Your lookup table would need to contain just enough to match, but not so much that you mis-match. Something like this:

You can then use a COUNTIF with CONTAINS like this

=COUNTIF([Company name (free text)]:[Company name (free text)], CONTAINS([Company name to look for]@row, @cell))

The part in bold could be a cross sheet reference.

You could hide the lookup column and instead have the full correct name displayed.