The formula required is a *slightly* complicated array formula.

Here is your spreadsheet with the formula showing the expected results:

This formula needs to be array-entered in `AE6`

and then filled/copy-pasted down and to the right as far as required):

```
{=SUM(IFERROR(INDEX(6:6,N(IF(1,COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1))+(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R)))/$I$2,0))}
```

**Explanation:**

The prettified version of the formula is as follows:

```
{=
SUM(
IFERROR(
INDEX(
(6:6),
N(IF(1,COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1))
+(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R))
)/$I$2,
0
)
)}
```

The formula is a lot easier to understand if you consider that for a 12 month duration and a 1 month delay, the second argument of the first `INDEX()`

is roughly equivalent to:

```
COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-{1,2,3,4,5,6,7,8,9,10,11,12}-1
```

The formula basically works by generating an array of offsets to access the orders of the previous `I2`

number of months, delayed by `I1`

months, relative to the current cell.

Stepping through the formula in `AK6`

should make the above clearer:

`COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))`

→ `{1,2,3,4,5,6,7,8,9,10,11,12}`

`COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-{1,2,3,4,5,6,7,8,9,10,11,12}-$I$1`

→ `{37}-({31}-{6})-{1,2,3,4,5,6,7,8,9,10,11,12}-1`

→ `{10,9,8,7,6,5,4,3,2,1,0,-1}`

`N(IF(1,{10,9,8,7,6,5,4,3,2,1,0,-1}))`

→ `N({10,9,8,7,6,5,4,3,2,1,0,-1})`

→ `{10,9,8,7,6,5,4,3,2,1,0,-1}`

`{10,9,8,7,6,5,4,3,2,1,0,-1}+({10,9,8,7,6,5,4,3,2,1,0,-1}>=COLUMN($R:$R))`

→ `{10,9,8,7,6,5,4,3,2,1,0,-1}+({10,9,8,7,6,5,4,3,2,1,0,-1}>={18})`

→ `{10,9,8,7,6,5,4,3,2,1,0,-1}+{0,0,0,0,0,0,0,0,0,0,0,0}`

→ `{10,9,8,7,6,5,4,3,2,1,0,-1}`

`INDEX((6:6),{10,9,8,7,6,5,4,3,2,1,0,-1})/$I$2`

→ `INDEX(6:6,{10,9,8,7,6,5,4,3,2,1,0,-1})/12`

→ `{24000,0,20000,0,0,"Opportunity Name1","bWmd1","Col C val","Col B val","Col A val","Col A val",#VALUE!}/12`

→ `{2000,0,1666.67,0,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}`

`SUM(IFERROR({2000,0,1666.67,0,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!},0))`

→ `2666.67`

`INDEX(6:6,N(IF(1,`*expression*)))

is a required hack^{*} in order to force Excel to return an array for *expression*

since the second argument of `INDEX()`

is evaluated to a single value by default. Using just `INDEX(6:6,`*expression*)

in `AK6`

would lead to

`INDEX((6:6),COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1+(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R)))`

→ `INDEX(6:6,37-(31-6)-COLUMN($A$6:$L$6)-1+(37-(31-6)-COLUMN($A$6:$L$6)-1>=18))`

→ `INDEX(6:6,12-`*1*-1+(12-*1*-1>=18))

→ `INDEX(6:6,10)`

→ `24000`

since inside an expression returning a single value, `COLUMN(`*multi-cell-range*)

returns the column of the first cell of the range.

The `+(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R))`

adjusts for the gap between the order tables for 2018 and 2019 (column `R`

). Note that the `N(IF(`

hack is not required in this case as the previously used hack has already forced an array evaluation of the second argument of `INDEX`

and thus the `COLUMN()`

function evaluate to arrays.

The `IFERROR()`

function is required in case the formula exists in a cell close to the left side of the sheet, resulting in either accessing text, or attempting to access a cell to the left of column `A`

.

**Notes:**

- The prettified formula actually works if entered.
- The brackets around
`(6:6)`

in the prettified version are required to force the `6:6`

to remain on its own line.

**Caveats:**

**There can be no numbers in the cells of the n columns to the left of the orders** (where n is specified by the value in `I2`

). If there are any numbers there, the formula, as is, will include them in the revenue calculation.
**There can be no gap between the Dec 2018 Revenue and the Jan 2019 Revenue columns.** The formula can be modified to allow for such a gap if one is desired.
- The gap between the two orders tables (
`R:R`

) must remain exactly one column wide. Otherwise, the formula, as is, will break.
- The inter- orders tables gap cannot contain any numbers. Otherwise, they will be treated as additional orders.

_{* The explanation of exactly why the hack works will have to wait until I figure it out for myself first ;-)}