Having issues with Formula!!!

[JB2385]

I am going to break down how I am understanding a portion of this formula below thus will lead to my question.

Formula:

=IF(AND(B$13<>"",$A14<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"1/","")=TRIM(SUBSTITUTE($A14,"Site","")))*($D$3:$D$10=B$13)),"")

The portions of the formula =TRIM(SUBSTITUTE($A14,"Site","") is essential taking out "Site" from the cell and leaving the Site number and in this case its reading it as 1 instead of Site 1. Please correct me if I am wrong.

My question is how can I had a second =Trim(substitute like above or do I not need it? My assumption would be like this (below). Again correct me if I am wrong.

=IF(AND(F$13<>"",$A14<>""),SUMPRODUCT((SUBSTITUTE($A$3:$A$10,"/5","")=TRIM(SUBSTITUTE($A14,"Site","")=TRIM(SUBSTITUTE($F13,"Ford 1.1","")))*($E$3:$E$10=F$13)),""))

In my master tracker I have another substitution I need input in F14 and the example above is how I was thinking it would be implemented however, I am receiving #VALUE! in the cell F14 instead of the number. The "Ford 1.1" is what I am trying to substitute so the formula will only count Type III instead of "Ford 1.1". I am using the first formula in all my other cells except column F. I hope this was descriptive enough. Please let me know if there any questions.

 

[ExceLoki]

here's a way you could do it that's not specific for a particular make/model
-----------------

4-26-23.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	DISTRICT / SITE	PARKING SLOT #	VIN	MAKE	MODEL				Chevrolet	Toyota	Dodge	Ford 1.0	Ford 1.1 Type III
3	1/1	120	JH4KA3170LC006787	Chevrolet	2.6			site 1	2	2	1	1	1
4	1/1	11	JS1RF16C442100152	Ford 1.1	Type III			site 2	0	0	0	0	0
5	1/1	12	2C4GM68475R667819	Dodge	ORV			site 3	0	0	0	0	0
6	1/1	65	1P4GH44R0RX359386	Chevrolet	2.1			site 4	0	1	0	0	0
7	1/1	12	1P4GH44R0RX359386	Toyota	ORV II
8	1/1	1	1GBJ7D1B4BV132373	Ford 1.0	Type III
9	1/4	66	JH4KA2640GC004861	Toyota	ORV II
10	1/1	41	JH4CC2640NC004693	Toyota	ORV II
Sheet2

Cell Formulas
Range	Formula
I3:M6	I3	=IF(AND(I$2<>"",$H3<>""),SUMPRODUCT(--(SUBSTITUTE($A$3:$A$10,"1/","")=SUBSTITUTE($H3,"site ","")),IFERROR(IF(MATCH(I$2,$D$3:$D$10,0),--(I$2=$D$3:$D$10)),--(I$2=CONCATENATE($D$3:$D$10," ",$E$3:$E$10)))),"")