Forum Discussion
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 months of sales ). Right now I am simply using sum formula where I need to adjust the range for past year every time I enter next month, I'm looking for some dynamic formula where based on sales available for 2024, it should limit the exact no. of months for 2023 as well in the calculation. Thanks in advance!
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.
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
What does row 1 contain? Dates formatted as mmm-yy, or text values?
- that's the date formatted as mmm-yy
Thanks!
In T2:
=SUMIFS($A2:S2, ">="&DATE(2023,1,1), $A2:S2, "<="&DATE(2023,2,2))
In U2:
=SUMIFS($A2:S2, ">="&DATE(2024,1,1), $A2:S2, "<="&DATE(2024,2,2))
Fill down.
