Formula calculation dependent on drop down list

[Lil Stinker]

I know it must be possible to have two different formulas calculate in the same cell based on the result chosen from a drop down list but I can't seem to make it work. I have a form where I need the subtotal to calculate for monthly prorating or weekly prorating between a start and end date differently based on whether the user is selecting "Months" or "Weeks" from the drop down.

The way these totals are reached for the monthly proration is somewhat convoluted. In order to receive accurate proration, I have to break it out in hidden columns off from the main table. For the weekly prorating, it is simply a matter of multiplying the unit rate*weekly term*quantity from the main table. Note, the E column only produces a decimal value when "Weeks" is chosen by design. Due to the convoluted monthly calculations, it is easier to have the E column show a simplified, common language breakout since the calculations are handled in the background.

I tried to use an IF statement but it only results in #VALUE for reasons beyond my understanding.
IF(E$1="Months",(D3*H8+K8*I8+L8*J8)*C3),IF(E$1="Weeks",(D3*E3*C3))

ORDER FORM -formula tests- 2022.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Description	QTY	Unit Rate	Weeks	Item Subtotal		[ Term ]
2	Pickup Date	WiFi Router	2	200.00	1.71	$171.40		Months
3	12/29/22	MiFi Hotspot	1	100.00	1.71	#VALUE!		Weeks
4	Billing Start
5	02/24/22
6	Billing End							Months	Weeks	Days	Weekly Rate	Daily Rate	Subtotal
7	03/08/22							0.00	1.00	5.00	50.00	7.14	171.40
8	Return Date							0.00	1.00	5.00	25.00	3.57	42.85
9	04/01/22
10
11					Subtotal	#VALUE!
12					Discount	$0.00
13					Net Amount	#VALUE!
14					TOTAL AMOUNT	#VALUE!
Sheet2

Cell Formulas
Range	Formula
E2:E3	E2	=IF(E$1="Weeks",ROUNDDOWN((DATEDIF($A$5,$A$7,"d")/7),2),IF(OR($B2="",AND(E$1<>"Months")),"",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(E$1="Months",DATEDIF($A$5,$A$7,"m")&"m,"&INT(($A$7-EDATE($A$5,DATEDIF($A$5,$A$7,"m")))/7)&"w,"&MOD($A$7-EDATE($A$5,DATEDIF($A$5,$A$7,"m")),7)&"d",INT(($A$7-$A$5)/7)&"w,"&MOD($A$7-$A$5,7)&"d"),"0m,",""),"0w,",""),",0d","")))
F2	F2	=IF(ISBLANK(C2),"",(D2*H7+K7*I7+L7*J7)*C2)
F3	F3	=IF(E$1="Months",(D3*H8+K8*I8+L8*J8)*C3),IF(E$1="Weeks",(D3*E3*C3))
H7:H14	H7	=IF(ISBLANK(C2),"",DATEDIF($A$5,$A$7,"M"))
I7:I14	I7	=IF(ISBLANK(C2),"",INT(($A$7-EDATE($A$5,DATEDIF($A$5,$A$7,"m")))/7))
J7:J14	J7	=IF(ISBLANK(C2),"",MOD($A$7-EDATE($A$5,DATEDIF($A$5,$A$7,"m")),7))
K7:K14	K7	=IF(ISBLANK(C2),"",(ROUND(D2/4,2)))
L7:L14	L7	=IF(ISBLANK(C2),"",(ROUND(K7/7,2)))
M7:M14	M7	=IF(ISBLANK(C2),"",(D2*H7+K7*I7+L7*J7)*C2)
F11	F11	=SUBTOTAL(109,Table22[Item Subtotal])
F13	F13	=(F11-(F11*F12))
F14	F14	=F13

Cells with Data Validation
Cell	Allow	Criteria
E1	List	=$H$1:$H$3

 

[StephenCrump]

F3: =IF(E$1="Months",(D3*H8+K8*I8+L8*J8)*C3,IF(E$1="Weeks",D3*E3*C3))

(Your formula close the first IF() too early)

 

[Lil Stinker]

F3: =IF(E$1="Months",(D3*H8+K8*I8+L8*J8)*C3,IF(E$1="Weeks",D3*E3*C3))

(Your formula close the first IF() too early)

D'oh! ?

Thank you!