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]
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]