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

Thanks, sorry! https://docs.google.com/spreadsheets/d/1vy_xWKHpXD5IB6cAEa0HD9gqmuXuhGl9/edit?usp=sharing&ouid=110359960991872949495&rtpof=true&sd=true is an updated link. Earlier I could add an attachment here, but that no longer seems to be possible.

HansVogelaar

MVP

Feb 23, 2022

SanderHaak

Still no luck.

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?