Formula help needed

[AKuma0411]

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!

[#2]

@AKuma0411 

What does row 1 contain? Dates formatted as mmm-yy, or text values?

[#3]

that's the date formatted as mmm-yy

[#4]

@AKuma0411 

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)
)

[#5]

@AKuma0411 

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.

[#6]

@Patrick2788  Is there any prerequisites to execute this formula, because I'm not able to run this formula it gives me #Name? error

[#7]

@HansVogelaar  I'm getting error while executing this logic. can you help me fix this?

[#8]

@AKuma0411 

My formula uses 2 named items:

[#9]

@AKuma0411 

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.

[#10]

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!

[#11]

@AKuma0411 

Would it be OK to use the current month (January at the time of writing) as the cutoff point?

[#12]

yes, that's fine

[#13 · Accepted solution]

@AKuma0411 

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:

[#14]

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.

[#15]

@AKuma0411 

You're welcome! Glad it worked out.