Feb 23 2022 04:25 AM - edited Feb 23 2022 05:35 AM
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:
=INDEX(Targets!S:AD;MATCH(1;(Targets!R:R=Dashboard!$C$4)*(Targets!Q:Q=Dashboard!$B10);0);MATCH($C$5;Targets!$S$5:$AD$5))
C4 = Country
C5 = Month
Thanks in advance!
Sander
Feb 23 2022 05:27 AM
I get "Access denied" when I click your link.
Feb 23 2022 05:36 AM
Thanks, sorry! Here is an updated link. Earlier I could add an attachment here, but that no longer seems to be possible.
Feb 23 2022 05:53 AM
3rd time is the charm? Should be available for anyone with this link
Feb 23 2022 06:17 AM
SolutionThanks, 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.
Feb 23 2022 06:21 AM
Feb 23 2022 06:29 AM
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.
Feb 23 2022 06:17 AM
SolutionThanks, 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.