sumif exact match string text

[coralie]

Hello,
I am bit stuck with my formula and hoping you guys can help me find a solution

I have 2 columns containing strings of text - for example:

C	D
Code	Region
Abcd15/22 | Bcd2/16 | Bcd21/04 | Bcd22/04 | def6H/04 | Bcd/04 |H01q1/00 | Gos1/44	JP4685349B2 | CN1316677C | DE60222772D1 | EP1407511A1 | CN1524318A

<tbody>
</tbody>

Please note that cell c should be linked to each region in cell D: Abcd15/22 is linked to Jpxxx, CNxxx, DExxx, EPxx, CNxxx etc...


I have a vba code to clean and split both columns as per below:

Sheet1

c	d	e	f	g	h	i	j
code 1	code 2	code 3	code 4	code 5	code 6	code 7	code 8
Abcd15	Bcd2	Bcd21	Bcd22	def6H	Bcd	H01q1	Gos1

<tbody>
</tbody>

Sheet2

c	d	e	f	g
region1	region2	region3	region4	region5
JP	CN	DE	EP	CN

<tbody>
</tbody>

I have a sheet counting the occurence of each region using =(LEN(C4)-LEN(SUBSTITUTE(C4; $D$2; "")))/LEN($D$2)

Now, I am trying to sum the region occurence for each code and I am using: SUMIF('Region count'!$C:$C;"*"&A3&"*";'Region count'!$D:$D) -> Array formula (where col C is the "unclean" string of text, Col A is the list of all codes cleaned and col D the count of occurence for region JP)

<tbody>
</tbody>

I had to do a few "arrangements" already... counting the number of charcters to add a space as some of the codes are too similar (ex: G01S), now i have an issue with codes like G01S1 suming up number of occurence from G01S13, G01S17, etc...

Is there a way to use sumif for exact match? If any other suggestion please let me know.

Also, as I am trying to create a template report hence I need everything to be linked and reference.

Last thing, my file is already running slow but i only have 7 sheets, formulas & array and 2 short vba code. Is there anything I can do to make if run better?

Hope my explanations are clear, if not please let me.

Thanks a lot for your help,

CORALIE

<tbody>
</tbody>