Forum Discussion

SanderHaak's avatar
SanderHaak
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's avatar
    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's avatar
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.

    • SanderHaak's avatar
      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's avatar
        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.

Resources