SOLVED

Dynamic YTD with conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-3203604%22%20slang%3D%22en-US%22%3EDynamic%20YTD%20with%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3203604%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20for%20some%20help%20with%20YTD%20(Year-to-date)%20calculations.%20I%20have%20a%20tab%20with%20targets%2C%20a%20tab%20with%20actuals%2C%20and%20then%20a%20sort%20of%20dashboard.%20On%20the%20dashboard%20the%20user%20can%20select%20the%20country%20and%20the%20reporting%20month.%20I'm%20looking%20to%20find%20a%20formula%20that%20can%20calculate%20the%20YTD%20target%2C%20based%20on%20the%20choices%20of%20the%20user.%20In%20the%20attached%20file%20I've%20recreated%20the%20situation%2C%20I%20have%20a%20workaround%20at%20the%20moment%20but%20I'd%20rather%20avoid%20that.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFile%20can%20be%20found%20%3CA%20href%3D%22https%3A%2F%2Fdocs.google.com%2Fspreadsheets%2Fd%2F1vy_xWKHpXD5IB6cAEa0HD9gqmuXuhGl9%2Fedit%3Fusp%3Dsharing%26amp%3Bouid%3D110359960991872949495%26amp%3Brtpof%3Dtrue%26amp%3Bsd%3Dtrue%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehere%2C%3C%2FA%3E%20unfortunately%20since%20I%20had%20to%20share%20it%20through%20Google%20drive%2C%20the%20document%20becomes%20Google%20sheets%20instead%20of%20Excel.%20Original%20formula%20I%20used%20was%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(Targets!S%3AAD%3BMATCH(1%3B(Targets!R%3AR%3DDashboard!%24C%244)*(Targets!Q%3AQ%3DDashboard!%24B10)%3B0)%3BMATCH(%24C%245%3BTargets!%24S%245%3A%24AD%245))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EC4%20%3D%20Country%3C%2FP%3E%3CP%3EC5%20%3D%20Month%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESander%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3203604%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3203899%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20YTD%20with%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3203899%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1097549%22%20target%3D%22_blank%22%3E%40SanderHaak%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20get%20%22Access%20denied%22%20when%20I%20click%20your%20link.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3203940%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20YTD%20with%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3203940%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20sorry!%20%3CA%20href%3D%22https%3A%2F%2Fdocs.google.com%2Fspreadsheets%2Fd%2F1vy_xWKHpXD5IB6cAEa0HD9gqmuXuhGl9%2Fedit%3Fusp%3Dsharing%26amp%3Bouid%3D110359960991872949495%26amp%3Brtpof%3Dtrue%26amp%3Bsd%3Dtrue%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EHere%3C%2FA%3E%20is%20an%20updated%20link.%20Earlier%20I%20could%20add%20an%20attachment%20here%2C%20but%20that%20no%20longer%20seems%20to%20be%20possible.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3203980%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20YTD%20with%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3203980%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1097549%22%20target%3D%22_blank%22%3E%40SanderHaak%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStill%20no%20luck.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

7 Replies

@SanderHaak 

I get "Access denied" when I click your link.

@Hans Vogelaar 

 

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

@SanderHaak 

Still no luck.

@Hans Vogelaar 

 

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=11035...

best response confirmed by SanderHaak (Occasional Contributor)
Solution

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

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?

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