Dynamic range adding help

[themev]

We are forecasting a fee to charge every year at some point after making a sale. Lets just say that the charge will be $10 per unit sold and be charged forever. We aren't sure what year it will kick in though so it could be 1 year after the sale, 2 years, 3 years etc. Some more details are below.
Here is the how the sheet looks
Cell B1 = $10 (the charge per unit)
B2-F2 have the years 2022-2026
B3-F3 have the units sold that year
B4-F4 have the cumulative units
B5 indicates which year the service charge should kick in = lets say 2 years, which would mean that in 2024, the units sold in 2022 will all be charged the $10 fee. In 2025, the units sold in both 2022 and 2023 would be charged $10 per unit. In 2026, the units sold in 2022-2024 would be charged $10.
I would want to build a formula to put into B6-F6 to calculate the fee, but to be able to change if B5 changes to 1 year or 3 years etc.

Service charge	$ 10
Year	2022	2023	2024	2025	2026
Units Sold	5	8	12	16	20
Cumulative units	5	13	25	41	61
Service charge year	2
Service charge fee

I am at a loss of how to even go about building this. Is this possible? Sorry, my work PC will not allow me to install XL2BB...
Thanks in advance for any guidance!

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this does what you want.

21 10 18.xlsm
	A	B	C	D	E	F
1	Service charge	$10
2	Year	2022	2023	2024	2025	2026
3	Units Sold	5	8	12	16	20
4	Cumulative units	5	13	25	41	61
5	Service charge year	2
6	Service charge fee			50	130	250
Fee

Cell Formulas
Range	Formula
B6:F6	B6	=IF(COLUMNS($B:B)>$B5,INDEX($B4:$F4,COLUMNS($B:B)-$B5)*$B1,"")

Reason for mentioning Excel version info is that if you are using Microsoft 365, the formula can be constructed to run more efficiently (& written a little shorter) as follows

=LET(c,COLUMNS($B:B),IF(c>$B5,INDEX($B4:$F4,c-$B5)*$B1,""))