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!
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) |