SOLVED

Dynamic YTD calculations

New Contributor

Hello,

 

Office 365, Windows computer

 

I'm looking to create dynamic Year-to-date calculations for our reporting metrics. I've attached a sample file to show the work I've been doing so far, and highlighting the expected outcome. 

 

I'm looking to have a drop-down select for any month, and year-to-date calculations for current and previous year will be based on that. I already have a method by using sum/offset/match. This works fine for staying in the current year, however I want to have the outcomes possible for different years as well. Currently I have a 2021 YTD column, and the calculation is only looking to 2021. When selecting the reporting month in cell C4, the calculations change based on that month. 

 

By looking at INDEX, and MATCH I've tried to create a method to look at the first month of the selected reporting year, but the calculations give errors each time. 

 

So in short, I would like a drop-down menu where the user can select any month between 2020-2022, and the YTD values and columns are changed based on that. I could do it the way I have it set-up now, but it would require a rebuild each year and fixed columns. 

 

- When selecting reporting month as June 2022, the report would show YTD 2022 and YTD 2021 up until June '21.

- When selecting the reporting month as December 2021, the report would show YTD 2021 and YTD 2020 up until December '21.

 

Hopefully my question is clear enough, I'd be happy to provide more context!

 

Best,

Sander

2 Replies
best response confirmed by SanderHaak (New Contributor)
Solution

@SanderHaak 

You can simplify by using SUMPRODUCT() function

 

YTD=2021

=SUMPRODUCT((MONTH(Q8:AN8)<=MONTH(Q4))*(YEAR(Q8:AN8)=YEAR(Q4))*Q9:AN9)

You can use this logic to adjust to other periods (Years or Months) by adding or subtracting.

Find attached a solution

 

 

Hi Juliano,

Thanks for your response, this solved my problem and works great

Best,
Sander