Forum Discussion
Solved
Formula help needed
Hi everyone, Is there a formula in excel which can only sum(Jan+Feb sales) to calculate YTD 2023 and then for YTD 2024 (let say I have Jan, feb sales available, so it should just sum the first 2 ...
All this setup requires you to do is add the following as a named item called 'YTD' to your name manager:
=LAMBDA(Data_2023, Data_2024, LET( cols, XMATCH(TRUE, ISNUMBER(TAKE(Data_2024, 1)), , -1), multiplier, SEQUENCE(cols, , 1, 0), Total_2023, MMULT(TAKE(Data_2023, , cols), multiplier), Total_2024, MMULT(TAKE(Data_2024, , cols), multiplier), diff, Total_2024 - Total_2023, HSTACK(Total_2023, Total_2024, diff) ) )Then you can call it at the sheet level and specify the ranges for 2023 and 2024 data and it will do its thing:
This is a 365 solution. For the arrangement I've added all the 2024 months even if no data yet. For the calculations it's better to add them now.
=LET(
cols, COLUMNS(YTD_2024),
multiplier, SEQUENCE(cols, , 1, 0),
Total_2023, MMULT(TAKE(YTD_2023, , cols), multiplier),
Total_2024, MMULT(TAKE(YTD_2024, , cols), multiplier),
diff, Total_2024 - Total_2023,
HSTACK(Total_2023, Total_2024, diff)
)
Patrick2788 Is there any prerequisites to execute this formula, because I'm not able to run this formula it gives me #Name? error