SumProduct help Multiple String Criteria

[Kemidan2014]

I am working on an export sheet for Access Query export so that i can graph some data. (access graphs can not handle what is needed)
I am working on the sheet in that holding all the formula tables to count for the graphs i need to produce.
I am stuck on the last table.

This table Columns which are Product GROUPS (meaning there can be anywhere between 1 and 5 part numbers that fall into that group.
The rows are Reasons for flow out. everything is text strings that i need to count up combinations of Part numbers & reason

Obviosly where i have 1 part number in a group i do a simple countifs and it adds up fine. Where i am losing my mind is where in the column that has up to 5 part numbers i typed out the part numbers above the group name in individual cells. I cant figure out how to get sumproduct to work correctly OR countifs, when i used countifs it always = zero which is not correct.

The table - Row 98 is simply my header row for the graph. i need to produce a stacked bar graph. it you look at column H youll see its not adding up right i should only have 1 under that column in Mis Sort row rest should be zeros but i have a 5 and some other numbers.

ComplaintMetricsQuery.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
93			881 LH Filler Cap Assy											CVT FSV
94			878 LH CHC Camry											2.5 Heater FSV
95			881 LH CHC					127 PST						2.0 Heater FSV
96		881 TCC	878 LH CHC		878 LH PST			947 PST	103 TCC No 2	115 TCC No 2		115 Suction		AEI FSV	152 TCC No 2
97	Why Ship by Part Model	878 TCC	878 RH CHC		878 RH PST	946 TCC No. 1	946 OP	946 PST	103 TCC No 1	115 TCC No 1		115 WP AS		ATF FSV	152 TCC No 1
98		GR TCC	GR CHC	881 FC	GR PST	TNGA TCC	TNGA OP	TNGA PST	103 TCC	115 TCC	115 OP	115 WP	136 PST	FSV	152 TCC	152 OP	TNGA OCV
99	4-Evils	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
100	Missed VI	0	1	0	0	0	0	2	0	0	0	0	0	0	0	0	0
101	VI NG	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
102	SWI Not Followed	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
103	SWI NG	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
104	Poka-Yoke Malf	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
105	No check in place	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
106	Mis-Sort	0	0	0	0	0	0	5	1	0	0	0	0	0	0	0	0
107	NTF	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
108	Cannot detect	0	0	0	0	0	0	2	0	0	1	0	0	0	0	0	0
Data

Cell Formulas
Range	Formula
B99:B108,E99:E108,I99:J108,L99:L108,O99:O108	B99	=SUMPRODUCT(ISNUMBER(SEARCH(B$97,ComplaintMetricsQuery!$K:$K))*ISNUMBER(SEARCH(Data!$A99,ComplaintMetricsQuery!$H:$H))+ISNUMBER(SEARCH(B$96,ComplaintMetricsQuery!$K:$K))*ISNUMBER(SEARCH(Data!$A99,ComplaintMetricsQuery!$H:$H)))
C99:C108,H100:H108,N99:N108	C99	=SUMPRODUCT(ISNUMBER(SEARCH(C$97,ComplaintMetricsQuery!$K:$K))*ISNUMBER(SEARCH(Data!$A99,ComplaintMetricsQuery!$H:$H))+ISNUMBER(SEARCH(C$96,ComplaintMetricsQuery!$K:$K))*ISNUMBER(SEARCH(Data!$A99,ComplaintMetricsQuery!$H:$H))+ISNUMBER(SEARCH(C$95,ComplaintMetricsQuery!$K:$K))*ISNUMBER(SEARCH(Data!$A99,ComplaintMetricsQuery!$H:$H))+ISNUMBER(SEARCH(C$94,ComplaintMetricsQuery!$K:$K))*ISNUMBER(SEARCH(Data!$A99,ComplaintMetricsQuery!$H:$H))+ISNUMBER(SEARCH(C$93,ComplaintMetricsQuery!$K:$K))*ISNUMBER(SEARCH(Data!$A99,ComplaintMetricsQuery!$H:$H)))
D99:D108,K99:K108,M99:M108,P99:Q108	D99	=COUNTIFS(ComplaintMetricsQuery!$K:$K,Data!D$98,ComplaintMetricsQuery!$H:$H,Data!$A99)
F99:G108	F99	=COUNTIFS(ComplaintMetricsQuery!$K:$K,Data!F$97,ComplaintMetricsQuery!$H:$H,Data!$A99)
H99	H99	=SUMPRODUCT(ISNUMBER(SEARCH(H$97,ComplaintMetricsQuery!$K:$K))*ISNUMBER(SEARCH(Data!$A99,ComplaintMetricsQuery!$H:$H))+ISNUMBER(SEARCH(H$96,ComplaintMetricsQuery!$K:$K))*ISNUMBER(SEARCH(Data!$A99,ComplaintMetricsQuery!$H:$H))+ISNUMBER(SEARCH(H$95,ComplaintMetricsQuery!$K:$K))*ISNUMBER(SEARCH(Data!$A99,ComplaintMetricsQuery!$H:$H)))

The source

DocType	SupplierCode	OfficialIssuanceDate	PartType	NAMC	Qty	WhyMake	WhyShip	DefectType	ProcessType	AisinName
QPR	0101-6	2/3/2023	OP	TMMAL	0	Design NG	4-Evils	Damage	AS	984 OP
QPR	0101-6	1/20/2023	OP	TMMWV	1	Chg Pt Not Controlled	Cannot detect	Function	AS	115 OP
QPR	0101-6	2/16/2023	CHC	TMMK-PWT	0	Process Design NG	Missed VI	Fit	RSN-AS	878 RH CHC
QPR	0101-6	2/14/2023	PST	TMMWV	0	Equipment NG	Mis-Sort	Damage	PST-MA	947 PST
QPR	0101-6	2/10/2023	TCC	TMMAL	0	Human Error	Mis-Sort	Dimension	MA	103 TCC No 1

 

[Kemidan2014]

Hazaah it works! So all i did was just Take the ranges and expand them to do 2-100 to cover us for 1 years worth of time.

Thank you very much sir.