Conditional Sumproduct Formula - Revenue Forecast

[chet645]

All, I am wondering if there is an easy fix to a conditional sumproduct formula I use to create revenue forecasts that incorporate contractual revenue increases over time. My formula correctly separates the different revenue streams (e.g. Spectacular B1, Spectacular B2, Spectacular C2, etc.), but I am having difficulty getting the formula to dynamically include revenue bumps (See columns "Esclation (%)" and "Mos. Btwn. Bumps"). Anyone ideas how to adjust my formula would much appreciated and will save me hours of time. Xl2bb mini sheet below:

Thanks in advance!

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX
2	SPECTACULARS - REVENUE	# Contracts	Revenue Start	Rev P/Unit ($)	Total Rev ($)	Esclation (%)	Mos Btwn. Bumps
3	Spectacular B1	2	5/31/2024	63,500	127,000	3.00%	12
4	Spectacular B1	2	5/31/2024	63,500	127,000	3.00%	12
5	Spectacular B1	2	5/31/2024	63,500	127,000	3.00%	12
6	Spectacular B1	2	9/30/2024	63,500	127,000	10.00%	60
7	Spectacular B1	2	10/31/2024	63,500	127,000	10.00%	60
8	Spectacular B2	2	5/31/2024	63,500	127,000	3.00%	12
9	Spectacular B2	2	5/31/2024	63,500	127,000	3.00%	12
10	Spectacular B2	2	5/31/2024	63,500	127,000	3.00%	12
11	Spectacular B2	2	9/30/2024	63,500	127,000	10.00%	60
12	Spectacular B2	2	10/31/2024	63,500	127,000	10.00%	60
13	Spectacular C1	2	5/31/2024	63,500	127,000	3.00%	12
14	Spectacular C1	2	5/31/2024	63,500	127,000	3.00%	12
15	Spectacular C1	2	5/31/2024	63,500	127,000	3.00%	12
16	Spectacular C1	2	9/30/2024	63,500	127,000	10.00%	60
17	Spectacular C1	2	10/31/2024	63,500	127,000	10.00%	60
18	Spectacular C2	2	5/31/2024	63,500	127,000	3.00%	12
19	Spectacular C2	2	5/31/2024	63,500	127,000	3.00%	12
20	Spectacular C2	2	5/31/2024	63,500	127,000	3.00%	12
21	Spectacular C2	2	9/30/2024	63,500	127,000	10.00%	60
22	Spectacular C2	2	10/31/2024	63,500	127,000	10.00%	60
23	Spectacular I-5A	2	5/31/2024	63,500	127,000	3.00%	12
24	Spectacular I-5A	2	5/31/2024	63,500	127,000	3.00%	12
25	Spectacular I-5A	2	5/31/2024	63,500	127,000	3.00%	12
26	Spectacular I-5A	2	9/30/2025	63,500	127,000	10.00%	60
27	Spectacular I-5A	2	10/31/2025	63,500	127,000	10.00%	60
28	Totals / Wtd. Avg.	50		63,500	3,175,000
29
30	Month		2/28/2024	3/31/24	4/30/24	5/31/24	6/30/24	7/31/24	8/31/24	9/30/24	10/31/24	11/30/24	12/31/24	1/31/25	2/28/25	3/31/25	4/30/25	5/31/25	6/30/25	7/31/25	8/31/25	9/30/25	10/31/25	11/30/25	12/31/25	1/31/26	2/28/26	3/31/26	4/30/26	5/31/26	6/30/26	7/31/26	8/31/26	9/30/26	10/31/26	11/30/26	12/31/26	1/31/27	2/28/27	3/31/27	4/30/27	5/31/27	6/30/27	7/31/27	8/31/27	9/30/27	10/31/27	11/30/27	12/31/27
31
32	Revenue
33	Spectacular B1		0	0	0	31,750	31,750	31,750	31,750	42,333	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917
34	Spectacular B2		0	0	0	31,750	31,750	31,750	31,750	42,333	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917
35	Spectacular C1		0	0	0	31,750	31,750	31,750	31,750	42,333	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917
36	Spectacular C2		0	0	0	31,750	31,750	31,750	31,750	42,333	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917
37	Spectacular I-5A		0	0	0	31,750	31,750	31,750	31,750	31,750	31,750	31,750	31,750	31,750	31,750	31,750	31,750	31,750	31,750	31,750	31,750	42,333	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917	52,917
38	Total Revenue		0	0	0	158,750	158,750	158,750	158,750	201,083	243,417	243,417	243,417	243,417	243,417	243,417	243,417	243,417	243,417	243,417	243,417	254,000	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583	264,583
Sheet1

Cell Formulas
Range	Formula
D33:AX37	D33	=SUMPRODUCT(($B$3:$B$27=$B33)*($D$3:$D$27<=D$30)*$F$3:$F$27)/12
D38:AX38	D38	=SUM(D33:D37)

 

[Saba Sabaratnam]

Hi

I have your data in my worksheet as displayed below.

To calculate price increase for this data layout, enter the following formula in

=(SUMPRODUCT(($A$2:$A$27=$B33)*($C$2:$C$27<=D$30)*$E$2:$E$27)/12)*(1+(SUMPRODUCT(($A$2:$A$27=$B33)*($C$2:$C$27<=D$30)*$F$2:$F$27)/COUNTIFS($C$2:$C$27,$D$30,$A$2:$A$27,$B$33)))

Kind regards

Saba

 

[Saba Sabaratnam]

You may have to change some of absolute references to relative copy it down and across in your data summary table.

Kind regards

Saba