Forum Discussion
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 formula is not spilled yet, but I am getting the error message. Can someone give me help? Thanks,
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) )
A dynamic array solution:
=LET(y, YEAR(TableData[Date]), PIVOTBY(, y, TableData[Amount], SUM))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, 🙂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) )
The criteria range must be a cell reference. No formulas. No operations.
- Hi, Lewin:
Do you mean YEAR(...) cause issue? Thanks,
- Thanks!
