Shifting Excel Formula for Quarter To Date Calculation?

[Redfly88]

Hi,

I am stuck on coming up for an excel formula that would calculate myQuarter to Date sales. I want the formula to return the sum for the months that have been concluded for that specific Quarter based on a drop-down pick list from my dashboard. (Future month sales are also in my excel table but are projections and not actuals) The fiscal year runs from March 1st to the end of February.

For example, if I picked the month of July, the formula should only return the sales for June and July since they both fall in quarter 2 and August has not concluded yet. However if I picked the month of May, the formula would then shift to give me total sales from March to May since May has concluded. I know I would need to use OFFSET, VLOOKUP, and potentially INDIRECT based on my month but I am not sure how I would account for the shift in quarters. My monthly data runs across in columns and has the industries in the rows.
[TABLE="width: 181"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 181"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Month[/TD]
[TD]Quarter[/TD]
[/TR]
[TR]
[TD]Mar[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Apr[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Jun[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Jul[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Aug[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Sep[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Oct[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Nov[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Dec[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[nardagus]

Hello,

I had exactly the same problem couple of days ago.
Maybe there is a better way to do that but...
I did two things.

I used a dictionary like you wrote in your post.
The other thing I did was to add a column helper where I placed a quarter number corresponding to the dictionary.

Data to sum up:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Quarter[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]mar[/TD]
[TD]1[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]apr[/TD]
[TD]1[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]may[/TD]
[TD]1[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]jun[/TD]
[TD]2[/TD]
[TD]2000[/TD]
[/TR]
</tbody>[/TABLE]

Then I used SUMIFS and INDEX+MATCH combo (I hate VLOOKUP)
=SUMIF(DATA!$B:$B;INDEX(DIC!$B:$B;MATCH(DASHBOARD!$A$1;DIC!$A:$A;0));DATA!$C:$C)

DASHBOARD!$A$1 - hold name of a month
INDEX+MATCH - will translate month name from DASHBOARD to a quarter number found in DIC worksheet
SUMIF - will do the rest

I hope it's clear.

 

[Redfly88]

Hey, really sorry for such a delayed response. Wanted to thank you for your feedback, although ended up doing something slightly different.

I esentially just used a SUMIFS formula to calculate the QTD sales if there is an "X" under the completed month and if it is quarter "1", "2", "3', or "4".

Just easier to implement for me.