SOLVED

# Formula help needed

Brass Contributor

# 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!

14 Replies

# Re: Formula help needed

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

# Re: Formula help needed

that's the date formatted as mmm-yy

# Re: Formula help needed

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

# Re: Formula help needed

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.

# Re: Formula help needed

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

# Re: Formula help needed

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

# Re: Formula help needed

My formula uses 2 named items:

# Re: Formula help needed

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.

# Re: Formula help needed

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!

# Re: Formula help needed

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

# Re: Formula help needed

yes, that's fine
best response confirmed by AKuma0411 (Brass Contributor)
Solution

# Re: Formula help needed

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:

# Re: Formula help needed

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.

# Re: Formula help needed

You're welcome! Glad it worked out.

1 best response

Accepted Solutions
best response confirmed by AKuma0411 (Brass Contributor)
Solution

# Re: Formula help needed

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: