automatically calculate results based off a raw data set

Hi All,

Attached is a report I am trying to build. Tab "Report" shows tables where I want results to auto populate based on the drop down lists in B2:B5.

The raw data is in the tab "Data".

I have been using sumifs formula to populate the cells, however have encountered a few issues:

1. the formula ends up being very long if I take into account that the state and channel drop down options can be either a state or total

2. I need the formula to work out what months to include in the Qtr and FYTD and CalYTD based on the month chosen. Eg. If I am reporting on Jun 2013, then the Qtr data would be Apr 2013 - Jun 2013 (rolling 3 months), the FYTD data would be from Apr 2013 - Jun 2013 (Japanese Fin Yr) and CalYTD would be Jan 2013 - Jun 2013. To make it more complicated, when comparing to last year it needs to be apples and apples, so the FYTD period needs to be Apr 2012 - Jun 2012, not Apr 2012 - Mar 2013, etc.

I'm not sure if this is better catered for through macros or formulas. I have posted this question in both this forum and the formulas forum

Book1.xlsx

Thanks