SOLVED

# Dynamic YTD with conditions

Occasional Contributor

# Dynamic YTD with conditions

Hello,

I'm looking for some help with YTD (Year-to-date) calculations. I have a tab with targets, a tab with actuals, and then a sort of dashboard. On the dashboard the user can select the country and the reporting month. I'm looking to find a formula that can calculate the YTD target, based on the choices of the user. In the attached file I've recreated the situation, I have a workaround at the moment but I'd rather avoid that.

File can be found here, unfortunately since I had to share it through Google drive, the document becomes Google sheets instead of Excel. Original formula I used was:

C4 = Country

C5 = Month

Sander

7 Replies

# Re: Dynamic YTD with conditions

Thanks, sorry! Here is an updated link. Earlier I could add an attachment here, but that no longer seems to be possible.

Still no luck.

# Re: Dynamic YTD with conditions

3rd time is the charm? Should be available for anyone with this link

best response confirmed by SanderHaak (Occasional Contributor)
Solution

# Re: Dynamic YTD with conditions

Thanks, that worked.

In Q10:

=SUMPRODUCT(Targets!\$D\$8:\$O\$20*(Targets!\$B\$8:\$B\$20=\$B10)*(Targets!\$C\$8:\$C\$20=\$C\$4)*(YEAR(Targets!\$D\$5:\$O\$5)=YEAR(\$C\$5))*(MONTH(Targets!\$D\$5:\$O\$5)<=MONTH(\$C\$5)))

Adjust the ranges if needed, but don't use entire columns, that slows down execution. Fill down to Q11.

# Re: Dynamic YTD with conditions

Thanks! That works great. Good tip by the way about the columns, I just used it for ensuring that if data is added below, the formulas would include that. Is there possibility with formulas to find the 'last value' other than using VBA's?

# Re: Dynamic YTD with conditions

It's possible, but it would add complexity to the formulas. I'd go with a large enough range, for example rows 8 to 200, or 8 to 1000. It doesn't matter if some or most of those rows are empty.