Sumif, Match, Index Match Issue

[helpmeplz_]

Images are below. Three tabs: 1) Report, 2) Store Vol, 3) Community Days.

J53 on Report is where the return value will be. Currently, in that cell is a combination of a VLookup and an HLookup. The Vlookup is pointing to A53 where a 1 is located to indicate Store #1 . It then finds Store #1 in the Store Vol sheet and returns the 42nd column which is the volume for that store for the year to date. Once it has the store's volume for the year to date, the formula divides it by the amount of business days in the year to get the average volume per day in the fiscal year. The problem is that some stores open in January, others open in February, or March and so on. Instead of it dividing by the number of days in the fiscal year that the store was open, it divides it by the total business days in the fiscal year to date. Does that make sense? So for example, in the current formula, if a store was open prior to fiscal year to date, it would have volume from the beginning of the fiscal year (September) all the way through to July (since August is not over yet it hasn't been reported). In this scenario, the formula would provide the correct average because it looks at the total business days fiscal year to date. But if the store had opened in December, it wouldn't have volume from September-November, but the formula would still count those business days and it would bring down the average volume per day. I need a formula that looks at the store volume tab, sees that a store had volume beginning in December, for example, and then goes to the Community Days and only counts the corresponding business days from December - fiscal year to date.

 

[helpmeplz_]

FYI, in the HLookup there is a reference to $A$9, that is a drop down box at the top of the sheet that has Jul-17, Jun-17, May-17, so on and so forth. The HLookup is looking at the drop box, seeing the Jul-17, and then finding that reference in the Community Days sheet and then going down one to provide the total business days fiscal year to date.

I would like the formula to somehow reference this cell so that when the drop down changes to another month it updates to divide by the months in that scenario that the business would have been open in that fiscal year to date.

 

[helpmeplz_]

Can anyone help me with this formula?

 

[Aladin Akyurek]

Try to post a small Excel readable input along with the desired output; see Attachments or https://app.box.com/s/soezox25h3w0q5s4rcyl.

 

[helpmeplz_]

Try to post a small Excel readable input along with the desired output; see Attachments or https://app.box.com/s/soezox25h3w0q5s4rcyl.

Here is the file:

2017-08-26_nhpsjvej | Files.fm.


Thanks so much!