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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Code[/TD]
[TD]Region[/TD]
[/TR]
[TR]
[TD]Abcd15/22 | Bcd2/16 | Bcd21/04 | Bcd22/04 | def6H/04 | Bcd/04 |H01q1/00 | Gos1/44[/TD]
[TD]JP4685349B2 | CN1316677C | DE60222772D1 | EP1407511A1 | CN1524318A[/TD]
[/TR]
</tbody>[/TABLE]

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[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[/TR]
[TR]
[TD]code 1[/TD]
[TD]code 2[/TD]
[TD]code 3[/TD]
[TD]code 4[/TD]
[TD]code 5[/TD]
[TD]code 6[/TD]
[TD]code 7[/TD]
[TD]code 8[/TD]
[/TR]
[TR]
[TD]Abcd15[/TD]
[TD]Bcd2[/TD]
[TD]Bcd21[/TD]
[TD]Bcd22[/TD]
[TD]def6H[/TD]
[TD]Bcd[/TD]
[TD]H01q1[/TD]
[TD]Gos1[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD]region1[/TD]
[TD]region2[/TD]
[TD]region3[/TD]
[TD]region4[/TD]
[TD]region5[/TD]
[/TR]
[TR]
[TD]JP[/TD]
[TD]CN[/TD]
[TD]DE[/TD]
[TD]EP[/TD]
[TD]CN[/TD]
[/TR]
</tbody>[/TABLE]


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)
[TABLE="width: 72"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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

[TABLE="width: 72"]
<tbody>[TR]
[/TR]
</tbody>[/TABLE]