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

 

[awoohaw]

Is "The source" a worksheet named "ComplaintMetricsQuery" if not you should post some of that data as well.
And if it is... can you please give the Column Headers and row numbers in "the Source" as your formulas from the top mini workbook ("Data")refer to "ComplaintMetricsQuery"?

 

[Kemidan2014]

Awoo, yes it is the worksheet named that in the same book.

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

 

[awoohaw]

@Kemidan2014
First, I made your formula a little cleaner by distributing the check in column H.
But, the second problem is that since you have a "" in row93, when you search for "" in the string, it gets a true with the is number function.
put an edit in front of that as well. I'll keep tooling around. but put this is row 99:

=SUMPRODUCT(
ISNUMBER(SEARCH(Data!$A99,ComplaintMetricsQuery!$H$1:$H$6))*
(
(--ISNUMBER(SEARCH(H$97,ComplaintMetricsQuery!$K$1:$K$6)))+
(--ISNUMBER(SEARCH(H$96,ComplaintMetricsQuery!$K$1:$K$6)))+
(--ISNUMBER(SEARCH(H$95,ComplaintMetricsQuery!$K$1:$K$6)))+
(--ISNUMBER(SEARCH(H$94,ComplaintMetricsQuery!$K$1:$K$6)))+
(--ISNUMBER(SEARCH(H$93,ComplaintMetricsQuery!$K$1:$K$6)))
)
)

 

[awoohaw]

Also, you omitted Row98 in your big formula. but try this:
=SUMPRODUCT(
ISNUMBER(SEARCH(Data!$A99,ComplaintMetricsQuery!$H$1:$H$6))*
(
($H$98<>"")*(--ISNUMBER(SEARCH(H$98,ComplaintMetricsQuery!$K$1:$K$6)))+
($H$97<>"")*(--ISNUMBER(SEARCH(H$97,ComplaintMetricsQuery!$K$1:$K$6)))+
($H$96<>"")*(--ISNUMBER(SEARCH(H$96,ComplaintMetricsQuery!$K$1:$K$6)))+
($H$95<>"")*(--ISNUMBER(SEARCH(H$95,ComplaintMetricsQuery!$K$1:$K$6)))+
($H$94<>"")*(--ISNUMBER(SEARCH(H$94,ComplaintMetricsQuery!$K$1:$K$6)))+
($H$93<>"")*(--ISNUMBER(SEARCH(H$93,ComplaintMetricsQuery!$K$1:$K$6)))
)
)

=SUMPRODUCT(
ISNUMBER(SEARCH(Data!$A99,ComplaintMetricsQuery!$H$1:$H$6))*
(
($H$98<>"")*(--ISNUMBER(SEARCH(H$98,ComplaintMetricsQuery!$K$1:$K$6)))+
($H$97<>"")*(--ISNUMBER(SEARCH(H$97,ComplaintMetricsQuery!$K$1:$K$6)))+
($H$96<>"")*(--ISNUMBER(SEARCH(H$96,ComplaintMetricsQuery!$K$1:$K$6)))+
($H$95<>"")*(--ISNUMBER(SEARCH(H$95,ComplaintMetricsQuery!$K$1:$K$6)))+
($H$94<>"")*(--ISNUMBER(SEARCH(H$94,ComplaintMetricsQuery!$K$1:$K$6)))+
($H$93<>"")*(--ISNUMBER(SEARCH(H$93,ComplaintMetricsQuery!$K$1:$K$6)))
)
)

 

[Kemidan2014]

yes as i explained Row 98 so its intentionally omitted that verbage is not used in my Access tables. it will only be used for the graph, rows 93 to 97 will be used for counting.

Oooh i didnt think of using Not <> thats clever i knew i had a syntax issue

 

[awoohaw]

so, taking that line out:
=SUMPRODUCT(
ISNUMBER(SEARCH(Data!$A99,ComplaintMetricsQuery!$H$1:$H$6))*
(
($H$97<>"")*(--ISNUMBER(SEARCH(H$97,ComplaintMetricsQuery!$K$1:$K$6)))+
($H$96<>"")*(--ISNUMBER(SEARCH(H$96,ComplaintMetricsQuery!$K$1:$K$6)))+
($H$95<>"")*(--ISNUMBER(SEARCH(H$95,ComplaintMetricsQuery!$K$1:$K$6)))+
($H$94<>"")*(--ISNUMBER(SEARCH(H$94,ComplaintMetricsQuery!$K$1:$K$6)))+
($H$93<>"")*(--ISNUMBER(SEARCH(H$93,ComplaintMetricsQuery!$K$1:$K$6)))
)
)

 

[awoohaw]

yes as i explained Row 98 so its intentionally omitted that verbage is not used in my Access tables. it will only be used for the graph, rows 93 to 97 will be used for counting.

Oooh i didnt think of using Not <> thats clever i knew i had a syntax issue

you said it was the header, not that it is to be omitted. But, i hope the formula still works. Does it?

 

[Kemidan2014]

It does, Does Sumproduct prohibit the use of entire column? the query will output dynamic range over time.

 

[awoohaw]

It does, Does Sumproduct prohibit the use of entire column? the query will output dynamic range over time.

no, i used the few cells i reference to make building the formula easier by seeing how the arrays calculate inside the formula (the F9 trick).

But, it has often been stated that referencing a huge range that will never be used can use up resources during calculations. It is one reason why tables are helpful as the ranges expand with the tables.