COUNTIF match first 10 characters in list

[benicholson100]

Hi,

I have a list of companies that I need to find how many times they occur in another list. The names are not exact so I have been looking to use the SEARCH function based on first 7 characters, however I cannot seem to merge this with a COUNTIF.

Below is one example; the name in List 1 is not the same as List 2 but they are the same company. How would I count how many times this occurs in List 2 by searching the first 10 characters?

Many thanks
Ben

Column / Row	A	B	C	D
1		List 1	List 2
2	COUNT	Names to find	No.	Name
3		THE ARTIST CLUB LTD	1	THE ARTIST CLUB
4			2	THE ARTIST CLUB
5			3	THE ARTIST CLUB

 

[steve the fish]

You cant use countif because it expects a range and left(range) isnt a range. Try sumproduct:

=SUMPRODUCT(--(LEFT(D3:D5,10)=LEFT(B3,10)))

 

[benicholson100]

Great - thank you very much for your reply. Much appreciated.

 

[Peter_SSs]

I cannot seem to merge this with a COUNTIF

If I have understood correctly, you could have used COUNTIF like this

Book1
	A	B	C	D
1		List 1	List 2
2	COUNT	Names to find	No.	Name
3	3	THE ARTIST CLUB LTD	1	THE ARTIST CLUB
4			2	THE ARTIST CLUB
5			3	THE ARTIST CLU
Count Partial Match

Cell Formulas
Range	Formula
A3	A3	=COUNTIF(D3:D5,LEFT(B3,10)&"*")

 

[benicholson100]

If I have understood correctly, you could have used COUNTIF like this



Yes, thank you Peter. Please could you explain how this works, namely the last part &"*" ?

Many thanks
Ben

 

[Peter_SSs]

The asterisk is a wild card, standing for any number of any characters.
So with the example, the formula =COUNTIF(D3:D5,LEFT(B3,10)&"*") becomes

=COUNTIF(D3:D5,"THE ARTIST*")

so it counts anything that starts with "THE ARTIST"