Forum Discussion

krdrossman's avatar
krdrossman
Copper Contributor
Jun 15, 2021
Solved

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

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, ...
excel
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Jun 16, 2021

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

     

krdrossman's avatar
krdrossman
Copper Contributor
Jun 16, 2021
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?
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 16, 2021

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

 

  • krdrossman's avatar
    krdrossman
    Copper Contributor
    Jun 16, 2021
    Thanks, Peter. I think I figured it out. Really appreciate your help.

Resources