SUM(SIGN(MMULT( Formula not working with an added criteria

[who am i]

Hello I am having trouble with the following formula:

=SUM(SIGN(MMULT(('Aggregate Formulas'!$BM$1:$BP$9999=$E10)*('Aggregate Formulas'!$C$1:$C$9999=FALSE())*('Aggregate Formulas'!$B$1:$B$9999=TRUE())*('Aggregate Formulas'!$AF$1:$AF$9999>=Q$7)*('Aggregate Formulas'!$AF$1:$AF$9999<=Q$8),TRANSPOSE(COLUMN('Aggregate Formulas'!$BM$1:$BP$1)^0))))

^^This produces a number as expected, but when I added one criteria: ('Aggregate Formulas'!$N$1:$N$9999="Yes") I get a value not available #N/A error. I am confused. Here is full new formula

=SUM(SIGN(MMULT(('Aggregate Formulas'!$BM$1:$BP$9999=$E10)*('Aggregate Formulas'!$N$1:$N$9999="Yes")*('Aggregate Formulas'!$C$1:$C$9999=FALSE())*('Aggregate Formulas'!$B$1:$B$9999=TRUE())*('Aggregate Formulas'!$AF$1:$AF$9999>=Q$7)*('Aggregate Formulas'!$AF$1:$AF$9999<=Q$8),TRANSPOSE(COLUMN('Aggregate Formulas'!$BM$1:$BP$1)^0))))


I am working to upload a sample sheet that doesnt include company info.


One Odd thing is that I can see the new criteria does have true and false results

A shot of the new column in question

 

[who am i]

New Product 320.xlsx
	Q
7	1/1/2024
8	12/31/2024
9	# Of Bids With Multiple Products
10	#N/A
11	10
12	0
13	21
Count BOD Yes

Cell Formulas
Range	Formula
Q10	Q10	=SUM(SIGN(MMULT(('Aggregate Formulas'!$BM$1:$BP$9999=$E10)*('Aggregate Formulas'!$N$1:$N$9999="Yes")*('Aggregate Formulas'!$C$1:$C$9999=FALSE())*('Aggregate Formulas'!$B$1:$B$9999=TRUE())*('Aggregate Formulas'!$AF$1:$AF$9999>=Q$7)*('Aggregate Formulas'!$AF$1:$AF$9999<=Q$8),TRANSPOSE(COLUMN('Aggregate Formulas'!$BM$1:$BP$1)^0))))
Q11:Q13	Q11	=SUM(SIGN(MMULT(('Aggregate Formulas'!$BM$1:$BP$9999=$E11)*('Aggregate Formulas'!$C$1:$C$9999=FALSE())*('Aggregate Formulas'!$B$1:$B$9999=TRUE())*('Aggregate Formulas'!$AF$1:$AF$9999>=Q$7)*('Aggregate Formulas'!$AF$1:$AF$9999<=Q$8),TRANSPOSE(COLUMN('Aggregate Formulas'!$BM$1:$BP$1)^0))))

 

[who am i]

New Product 320.xlsx
	N
1	Basis of Design?
2	Title
3	Yes
4	No
5	Yes
6	Yes
7	Unknown
8	Yes
9	Unknown
10	No
11	Yes
12	Yes
13	No
14	Unknown
15	Unknown
16	No
17	No
18	No
19	No
20	Yes
Aggregate Formulas

 

[who am i]

there were #N/a errors deep in the column N. changed array lookup to include iferror, then "".