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:
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.
Sorry, my bad.
n T2:
=SUMIFS($A2:S2, $A$1:S$1, ">="&DATE(2023,1,1), $A$1:S$1, "<="&DATE(2023,2,1))
In U2:
=SUMIFS($A2:S2, $A$1:S$1, ">="&DATE(2024,1,1), $A$1:S$1, "<="&DATE(2024,2,1))
Fill down.
- This formula is not dynamic though, because let's say I have my march 2024 sales added to the spreadsheet, I want my YTD 2023 to dynamically sum march 2023 sales, I don't want to manually change the dates, that's prone to mistakes
Thanks for responding!