Update Formula so it calculates correctly

[pyclen]

Hi there,

I have a spreadsheet (below, a simplified version without dropdowns and selections but accurately demonstrating what I want to achieve)

There are 3 tables, one where the tests are selected (top) and when what test is performed. The selectors are X, Y, Z for must have testing (not optional), and A, B, C for optional testing.
The second table is the table for the calculations for each timepoint, and the formula works fine w/o problem when there is a selection of X, Y, Z present, If only A, B, C, is peresent the calculation is wrong as the amount for the particular test should be zero w/o X, Y, Z.

The third table is the calculation for the optional testing, and the same formula is used but again gives the wrong result when no A, B, C is present.

The wrong results are in red in both tables. How can I modify the formula so i calculates correctly in these fields?

Any help is greatly appreciated

New Cost Sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2				Time [months]
3			Test	Release	1	3	6	9	12	18	24	36	48	60
4			ad	x	AC	AC
5			as	x	xy	xy
6			fd	x	xy	xy
7			ff	x	xy	xy
8			ft	x	xy	xy
9			gh	x	xy	xy
10
11
12
13			Notes	Test	Difficulty	Hourly rate	Setup Hours	Replicate Hours	Replicates	# of lots	Orientations	Package Configurations	Release	1	3	6	9	12	18	24	36	48	60
14				ad	avg	100	1	2	2	1	1	1	500	100	100	$0
15				as	avg	100	2	2	2	1	1	1	600	1000	1000	$0
16				fd	avg	100	3	2	2	1	1	1	700	1100	1100	$0
17				ff	avg	100	4	2	2	1	1	1	800	1200	1200	$0
18				ft	avg	100	5	2	2	1	1	1	900	1300	1300	$0
19				gh	avg	100	6	2	2	1	1	1	1000	1400	1400	$0
20													4500	6100
21
22	optional		Notes	Test	Difficulty	Hourly rate	Setup Hours	Replicate Hours	Replicates	# of lots	Orientations	Package Configurations		1	3	6	9	12	18	24	36	48	60
23				ad	avg	100	1	2	2	1	1	1		900
24				as	avg	100	2	2	2	1	1	1		200
25				fd	avg	100	3	2	2	1	1	1		300
26				ff	avg	100	4	2	2	1	1	1		400
27				ft	avg	100	5	2	2	1	1	1		500
28				gh	avg	100	6	2	2	1	1	1		600
29														2900
Sheet1

Cell Formulas
Range	Formula
M13:W13	M13	=IF(D3="","",D3)
M14:M19	M14	=IF(D4="","$0",PRODUCT($F14,(SUM($G14,(PRODUCT(H14:L14))))))
N14:N19	N14	=IF($E4="","$0",PRODUCT($F14,(SUM($G14,PRODUCT(COUNT(SEARCH({"X","Y","Z"},$E4)),$H14:$L14)))))
O14:O19	O14	=IF($F4="","$0",PRODUCT($F14,(SUM($G14,PRODUCT(COUNT(SEARCH({"X","Y","Z"},$F4)),$H14:$L14)))))
P14:P19	P14	=IF($G4="","$0",PRODUCT(F$14,(SUM($G14,PRODUCT(COUNT(SEARCH({"X","Y","Z"},$G4)),$H14:$L14)))))
D13	D13	=C3
D14:D19	D14	=IF(C4="","",$C4)
M20:N20,N29	M20	=SUM(M14:M19)
C22,N22:W22,E22:L28	C22	=C13
D22:D28	D22	=C3
N23:N28	N23	=IF($E4="","$0",PRODUCT($F14,(SUM($G14,PRODUCT(COUNT(SEARCH({"A","B","C"},$E4)),$H14:$L14)))))

 

[StephenCrump]

I think you want?

N14: =LET(c,COUNT(SEARCH({"X","Y","Z"},E4)),--IF(c,$F14*(G14+PRODUCT(c,H14:L14))))

If that's right, you can make similar adjustments to the other incorrect formulae.