Excel formula count occurances

[Biz]

Dear All,

I'm trying to construct formulas in Col G which matches Col H.
My criteria to add matches in a string is located in cell M2.

Your help would be greatly appreciated.

Kind Regards


Biz



Excel 2013 32 bit

	B	C	D	E	F	G	H	I	J	K	L	M
1​	Name	Category	Month	String		Cal	Should Be					Criteria
2​	Tom	Respite	1/11/2016​	Respond,Other,Reassessed		0​	3​					Respond,Other,Reassessed
3​	Tom	Other	1/11/2016​	Respond,Other,Reassessed		0​	3​
4​	Tom	Reassessed	1/11/2016​	Respond,Other,Reassessed		0​	3​
5​	BK	Respite	1/12/2016​	Respond		1​	1​
6​	Tom	Other	1/12/2016​	Other,Other		0​	2​
7​	Tom	Other	1/12/2016​	Other,Other		0​	2​
8​	Spider	A	1/12/2016​	A		0​	0​

<tbody>
</tbody>

Sheet: Sheet2

<tbody>
</tbody>

Excel 2013 32 bit

	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Category	Month	String		Cal	Should Be					Criteria
2	Tom	Respite	1/11/2016	Respond,Other,Reassessed		0	3					Respond,Other,Reassessed
3	Tom	Other	1/11/2016	Respond,Other,Reassessed		0	3
4	Tom	Reassessed	1/11/2016	Respond,Other,Reassessed		0	3
5	BK	Respite	1/12/2016	Respond		1	1
6	Tom	Other	1/12/2016	Other,Other		0	2
7	Tom	Other	1/12/2016	Other,Other		0	2
8	Spider	A	1/12/2016	A		0	0

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula G2 = SUMPRODUCT(--ISNUMBER( SEARCH("," & E2 & ",","," &SUBSTITUTE(TRIM($M$2),",",", ") & ","))) G3 = SUMPRODUCT(--ISNUMBER( SEARCH("," & E3 & ",","," &SUBSTITUTE(TRIM($M$2),",",", ") & ","))) G4 = SUMPRODUCT(--ISNUMBER( SEARCH("," & E4 & ",","," &SUBSTITUTE(TRIM($M$2),",",", ") & ","))) G5 = SUMPRODUCT(--ISNUMBER( SEARCH("," & E5 & ",","," &SUBSTITUTE(TRIM($M$2),",",", ") & ","))) G6 = SUMPRODUCT(--ISNUMBER( SEARCH("," & E6 & ",","," &SUBSTITUTE(TRIM($M$2),",",", ") & ","))) G7 = SUMPRODUCT(--ISNUMBER( SEARCH("," & E7 & ",","," &SUBSTITUTE(TRIM($M$2),",",", ") & ","))) G8 = SUMPRODUCT(--ISNUMBER( SEARCH("," & E8 & ",","," &SUBSTITUTE(TRIM($M$2),",",", ") & ","))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Biz]

Sorry mate you are right it's my error.
Have Happy Xmas and Happy New Year.

Biz

 

[Marcelo Branco]

Merry Christmas and Happy New Year



M.