Sumproduct Question

[aells]

I have a formula in a column S that looks like this =-IF(N83="Feed",O83*$E$33*$I$62*(P83/$I$60+$I$61),0)+R83

Column n = who transports
Column o = Pickups
Column P = round trip distance
Column R = Cost
Column D = Start Date

Cell E33 = Weeks in month
Cell i62 = rate - want to make this dynamic with price changing each month new cell E38
Cell i60 = speed - want to make this dynamic with price changing each month new cell E36
cell i61 = time (distance) - want to make this dynamic with price changing each month new cell E37

Currently, the calculation above is in column S

What I want to do is forecast these number in light blue column moving forward by month
=SUMPRODUCT(($N$83:$N$171="Feed")*($D$83:$D$171<=E$2))*((($O$83:$O$171)*E$33*E$38)*((($P$83:$P$171)/E$36+E$37)*($R$83:$R$171)))

This is not giving me any answer.

What am I missing? I am thinking I cannot have the single cell formula attached to the array, if that is the case how do i do this formula?

 

[GraH]

at first glance, there might be missing parentheses?

=SUMPRODUCT(($N$83:$N$171="Feed")*($D$83:$D$171<=E$2))*((($O$83:$O$171)*E$33*E$38)*((($P$83:$P$171)/[SIZE=5][COLOR=rgb(184, 49, 47)][B]([/B][/COLOR][/SIZE]E$36+E$37)[SIZE=5][COLOR=rgb(184, 49, 47)][B])[/B][/COLOR][/SIZE]*($R$83:$R$171)))

Do consider to provide the sample through the XL2BB add-on. Pictures do not really help and most of us do not like to type over data from pictures (tedious job).

 

[aells]

Financial model formula.xlsx
	A	B	C	D	E	F
2	12/1/2021			Dec-21	Jan-22	Feb-22
3			Recurring Feed Waste Intake (tons / mo)	846	257	433
4			Pipeline (tons / mo)	5,074	5,074	5,074
5			Pipeline Captured	0%	0%	5%
6			Pipeline Projection (tons / mo)	-	-	254
7			Total Feed Waste Intake (t)	846	257	686
8	SECURED RECURRING WASTE INTAKE
9			Secured Feed Revenue	$ 123,241	215,802	363,167
10			Hauling Revenue	$ 27,720	$ 27,720	$67,920
11			Total Secured Revenue ($ / mo)	$ 151,807	$ 243,522	$ 431,087
12	line item formula		Waste Feedstock	$ 14,750	$ 29,250	$45,966
13	=-IF(N83="Feed",O83*$E$33*$I$62*(P83/$I$60+$I$61),0)+R83		Hauling Costs	$ 44,702	0	=SUMPRODUCT(sumif($D$83:$D$171,F$2,(($O$83:$O$171)*f35*f38))*$n$83:$n$171="Feedback)*($p$83:$p$171)/$F$36+$F$37)*($r$83:$R$171)
14			Energy Costs	$ 19,265	$ 19,886	$34,438
15			Labor Costs	$ 12,690	$ 15,420	$26,477
16			Packaging Disposal	$ 2,382	$ 3,000	$ 8,919
17			Total Secured COGS ($ / mo)	$ 93,789	$ 67,557	$ 115,799
18			Secured Gross Profit ($ / mo)	$ 58,018	$ 175,966	$ 315,288
19			GM	38%	72%	73%
RecurringFeedWasteIntake

Cell Formulas
Range	Formula
E2	E2	=EOMONTH($A$2,1)
F2	F2	=EOMONTH($A$2,2)
D3	D3	=SUMIFS($G$83:$G$170,$D$83:$D$170,"<="&D$2)
E3:F3	E3	=SUMIFS($F$83:$F$171,$D$83:$D$171,"<="&E$2)
D4:F4	D4	=SUMIFS($G$83:$G$170,$D$83:$D$170,"="&"pipeline")
D6:F6	D6	=D5*D4
D7:F7	D7	=D3+D6
D9	D9	=SUMIFS($W$83:$W$170,$D$83:$D$170,"<="&D$2)
E9	E9	=SUMPRODUCT(IF($D$83:$D$171<=E2,($F$83:$F$171*$H$83:$H$171)-($F$83:$F$171*($J$83:$J$171)-0.1)))+E$3*E$35*E$57
F9	F9	=SUMPRODUCT(IF($D$83:$D$171<=F2,($F$83:$F$171*$H$83:$H$171)-($F$83:$F$171*$J$83:$J$171)))+F$3*F$35*F$57
D10	D10	=SUMIFS($AD$83:$AD$170,$D$83:$D$170,"<="&D$2)
E10	E10	=-SUMPRODUCT(--($D$83:$D$171<=E$2)*($N$83:$N$171="Feedback")*($O$83:$O$171*$AC$83:$AC$171))*E35
F10	F10	=-SUMPRODUCT(IF($D$83:$D$171<=F$2,($O$83:$O$171*$AC$83:$AC$171)))*F35
D11	D11	=SUM(D5:D10)
E11:F11	E11	=SUM(E9:E10)
D12,F12	D12	=-SUMIFS($Z$83:$Z$170,$D$83:$D$170,"<="&D$2)
E12	E12	=SUMPRODUCT(IF($D$83:$D$171<=E$2,($F$83:$F$171*$Y$83:$Y$171)))*E$35
D13	D13	=-SUMIFS($S$83:$S$170,$D$83:$D$170,"<="&D$2)
E13	E13	=SUMPRODUCT(($N$83:$N$171="Feedback")*($D$83:$D$171<=E$2))*((($O$83:$O$171)*E$35*E$38)*((($P$83:$P$171)/E$36+E$37)*($R$83:$R$171)))
D14,F14	D14	=-SUMIFS($T$83:$T$170,$D$83:$D$170,"<="&D$2)
E14	E14	=SUMIFS($K$83:$K$170,$D$83:$D$170,"<="&E$2)*E$42
D15,F15	D15	=-SUMIFS($U$83:$U$170,$D$83:$D$170,"<="&D$2)
E15	E15	=-SUMIFS($G$83:$G$170,$D$83:$D$170,"<="&E$2)*E$44
D16,F16	D16	=-SUMIFS($V$83:$V$170,$D$83:$D$170,"<="&D$2)
E16	E16	=SUMPRODUCT(IF($D$83:$D$171<=E$2,($F$83:$F$171*$H$83:$H$171)))*E$35*E43
D17:F17	D17	=SUM(D12:D16)
D18:F18	D18	=-D17+D11
D19:F19	D19	=D18/D11
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
DateStart	=RecurringFeedWasteIntake!$D$83:$D$170	D9:F10, D12:E16, F14:F16, F12, D3:F4
HaulingCosts	=RecurringFeedWasteIntake!$S$83:$S$170	D13
WeeklyVolume	=RecurringFeedWasteIntake!$F$83:$F$170	E16, E12, E9:F9, E3:F3

 

[aells]

Sorry I uploaded one part here is the other

Financial model formula.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
82	Date Start	Feed Price	Weekly Volume	Monthly Volume	Packaging	Packaging	Moisture	Water Tonnage	Monthly Feed Production	Who Pays	Who transports	Pickups / Week	Round Trip Distance	Equipment Type	Equip Cost	Hauling Costs
83	6/1/2021	$ 210	16	64	0%	-	70%	38	26	FeedBack	FeedBack	1	154	dedicated trailer	-	$ (2,366)
84	6/1/2021	$ 210	14.5	58	1%	1	45%	20	37	FeedBack	FeedBack	1	74	dedicated trailer	-	$ (1,438)
85	6/1/2021	$ 210	16	64	2%	1	45%	22	41	FeedBack	FeedBack	2	210	35-yard self-contained	-	$ (6,032)
86	6/1/2021	$ 210	5	20	2%	0	65%	11	9	RecycleWorks	RecycleWorks	2	-	-	-	$ -
87	6/1/2021	$ 210	3	10	10%	1	18%	1	8	RecycleWorks	RecycleWorks	2	-	-	-	$ -
88	6/1/2021	$ 210	2	8	2%	0	10%	-	8	RecycleWorks	RecycleWorks	2	-	-	-	$ -
89	6/1/2021	$ 210	2	8	3%	0	1%	(1)	8	RecycleWorks	RecycleWorks	2	-	-	-	$ -
90	6/1/2021	$ 210	10	40	5%	2	40%	11	27	RecycleWorks	RecycleWorks	2	-	-	-	$ -
91	8/1/2021	$ 210	62.5	250	1%	3	35%	62	186	FeedBack	FeedBack	6	70	35-yard self-contained	-	$ (8,352)
RecurringFeedWasteIntake

Cell Formulas
Range	Formula
K83:K91	K83	=(G83-I83)*(J83-0.1)
L83:L91	L83	=G83-I83-K83
P83	P83	=154
E83:E91	E83	=$P$67
G83:G91	G83	=F83*$E$35
I83:I91	I83	=H83*G83
S83:S91	S83	=-IF(N83="FeedBack",O83*$E$35*$I$63*(P83/$I$61+$I$62),0)+R83

Named Ranges
Name	Refers To	Cells
WeeklyVolume	=RecurringFeedWasteIntake!$F$83:$F$170	G83