Forum Discussion

Copper Contributor

Feb 23, 2022

Solved

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 countr...

excel

HansVogelaar
Feb 23, 2022
SanderHaak

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.

SanderHaak

Copper Contributor

Feb 23, 2022

HansVogelaar

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

https://docs.google.com/spreadsheets/d/1vy_xWKHpXD5IB6cAEa0HD9gqmuXuhGl9/edit?usp=sharing&ouid=110359960991872949495&rtpof=true&sd=true

HansVogelaar

MVP

Feb 23, 2022

SanderHaak

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.

SanderHaak
Copper Contributor
Feb 23, 2022
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?
- HansVogelaar
  MVP
  Feb 23, 2022
  SanderHaak
  
  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.