Forum Discussion

tanacal's avatar
tanacal
Brass Contributor
Oct 08, 2024
Solved

Help on Error message "There's a problem with this formula"

Hi,    I am looking for help to resolve this error message: I am trying to put in a spilled formula for the years 2025 - 2028 to sum by years.     I knew the current highlighted formu...
excel
  • tanacal's avatar
    tanacal
    Brass Contributor
    Oct 11, 2024

    SergeiBaklan 

     

    Thanks, it works.
    Can you make it more dynamic by spilling into the highlighted rows, not only one row? I knew I could copy down the formula, can it be spilled by one formula to all rows? Thanks, 🙂

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Oct 11, 2024

      tanacal 

      If without exotic and use the same formula

      =LET(
  months,  $BK$1:$BS$1,
  years,   $BE$1:$BI$1,
  data,    $BK$3:$BS$6,
  n,       SEQUENCE( ROWS(data) ),
  sums,    LAMBDA(v, MMULT(v, --(TRANSPOSE(YEAR(months)) = years) ) ),
  raw,     REDUCE(0, n, LAMBDA(a,i, VSTACK(a, sums(CHOOSEROWS(data,i))) ) ),
  DROP(raw,1)
)

      • tanacal's avatar
        tanacal
        Brass Contributor
        Oct 12, 2024

        SergeiBaklan 

         

        Wow, you are truly amazing! I knew Reduce function is powerful. My excel level is not high enough to use it like your fluency. This is my second time encountering this Reduce function. I am sure with my consistent effort, I will learn it to be some type of fluency, hopefully. ğŸ˜Š It took me some time to understand,follow and learn your logic in that outstanding formulas. Thanks, appreciated!

         

        Now I understand the logic behind this formula, however, I have experienced an error message. Would you mind helping me again? What have I done wrong? ğŸ˜¥

         

         

Resources