Dynamic sum of row data in non-contiguous columns based on changing date

[krdrossman]

On attached file, I am trying to build a formula for each row (starting at row 6) that will dynamically sum  the non-contiguous (alternate) columns starting with column J through column BE; however, the starting column needs to be dynamic such that it starts in the month after the latest month in which Actual (green column) data has been entered. In the example above, actual data has been entered through May 2021, so the formula should return the sum of the "budget" (odd) columns starting with Jun-21 (column T) through column BE. Next month, when Jun-21 actual data has been entered, the sum of the "budget" columns should automatically start wtih Jul-21 (column V) through column BE, and so on as additional months of actual data are entered.

Using PC Windows 10 Version 20H2 (OS Build 19042.1052) and Microsoft Office 365 Excel (Build 13929.20296 Click-to-run)

Thanks!

[#2]

@krdrossman 

I might be making some progress but using Excel 365.

[#3]

@krdrossman 

People tend to build cross-tabular models instead of IPO models,

I use a IPO approach in the attached file. Note: Due to normalization the original table had to be split in two tables.

[#4]

@Peter Bartholomew Your solution appears to deliver the desired result.  Is there anything else you believe is required?  And Thanks!

[#5]

How do I expand the range (# of rows in Column A) to accommodate down through row 18 ("DO NOT USE")? When I click F2 to edit, I can't use the mouse to pull the range down the addditional rows?

[#6 · Accepted solution]

@krdrossman 

That is because of the way in which I select a relative row out of the budget or actuals table.  The formula

= XLOOKUP(@ProjectName,ProjectName,RevenueBudget)

used within

= LET(
  projectBRevenue, XLOOKUP(@ProjectName,ProjectName,RevenueBudget),
  SUMIFS(projectBRevenue, DateLine, ">0"))

will only be extendable as far down as the named ranges ProjectName, RevenueActual and RevenueBudget are defined.  I tend to use Excel Tables for such problems and define the names in terms of structured references but it is possible to resize the ranges in Name Manager or even extend them down beyond the active range (if the formulas work and you need more explanation, I can provide that).

[#7]

Thanks, Peter. I think I figured it out. Really appreciate your help.