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:
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:
thank you! I was finally able to implement this, this is exactly what I was looking for, the values are changing dynamically with new sales input.
You're welcome! Glad it worked out.