Formula to capture MTD,QTD and YTD (depending on the respective quarter)

[melvin]

Hi all,

I have attached a file where I am trying to get formula for MTD, QTD, YTD once I use the week drop down

i.e. If I use week 4 of the year MTD will be first 4 weeks of the year and so will QTD

However if I choose week 48,
MTD will be from week 45
QTD will be from week week 40

Appreciate any assistance. This will get gold if I can automate this.

 

[Luke M]

Hi melvin,

The text strings of "Week x" are hard to work with for numerical comparison, so I created some helper cells with just numbers, but I got the formulas working I believe.

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[melvin]

thanks Luke, works perfect. Really good way of doing it. happy to buy you a drink mate if you are around Melbourne

Narayan,
Liked your login as well, however the MTD and YTD and QTD have to be restricted to the store i.e "C". i tried to do it by self but came as too many arguments. Appreciate your assistance

Thanks boys
Melv

 

[melvin]

Luke

If I had to count all the store with C's with the given period, would you be able to assist wit that. As I am trying to get avg item price this would be beneficial.

Appreciate it mate.

I have added the query in G2 to G26

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[melvin]

Thanks Narayan, works perfect. If you get sometime would you be able to assist with the Count formula that I have asked Luke for.

Appreciate a ton guys. Once I get the count my report will be fantastic. Thanks to you guys

Melv.

 

[NARAYANK991]

Hi ,

I am not able to understand your requirement ; what are the cells where you want a formula , and what values should the formula return ? Can you manually input these values in the appropriate cells , and then upload the workbook ?

Alternatively , post the cell addresses and their values in this thread.

Narayan

 

[melvin]

Hi Narayan,
I have highlighted the required section in Green.

If I want to count Store C for YTD for week 15, It will add up week 5 & 6 from week 1 to week 15 , "where it is tagged to the week and the store"

As the week changes and store changes the result changes.

 

[melvin]

Luke

If I had to count all the store with C's with the given period, would you be able to assist wit that. As I am trying to get avg item price this would be beneficial.

Appreciate it mate.

I have added the query in G2 to G26

Luke the formula I had for the cells G23 to G26 should have been a COUNT and not a SUM. i am looking to count the numbers when you choose MTD/QTD/YTD.

I just realised I asked for the wrong info after reading Narayan's response.

Thanks.

 

[NARAYANK991]

Hi ,

Since there are 3 stores named C , shouldn't the count be 3 for every week ?

Narayan

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[melvin]

that's exactly what I wanted. Thanks mate.

Apologies if I did not relay the information properly. Cheers Narayan