For dynamic table and chart: how to sumifs with horizontal and vertical multiple criteria

Hi to all,
I tried to search the forum (and all the internet) but I can't seem to find what I need and I am getting desperate!

I have a large amount of data, and need to create a dynamic table which will link to a dynamic graph thus creating a dashboard.

So for example the data has multiple criteria as columns, but also as lines (units per month):

Col A Col B Col C Col D Col E Col F Col G Col H Col I Col J Col K Col L Col M Col N Col O Col P
Region Year Scenario Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
Line 1 Region 1 2013 Actual 100 101 102 103 104 105 106 107 108 109 110 111 1266
Line 2 Region 2 2013 Actual 112 113 114 115 116 117 118 119 120 121 122 123 1410
Line 3 Region 3 2012 Forecast 124 125 126 127 128 129 130 131 132 133 134 135 1554
Line 4 Region 4 2012 Budget 136 136 136 136 136 136 136 136 136 136 136 136 1632


I then need to create a dynamic table as below linked to the Region & Year (column A & B) Drop down list, so I can link it to a graph and make it dynamic.

Scenario Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total
Actual
Forecast
Budget


What can I use as formula? I tried SUMIFS (I have excel 2007 and 2010) but I can't make it work on horizontal ranges!
Pleaseeeeeeeeeeeeeeeeeeee help!
Thank you
Gabriella

Hi... no one can help me?

It's difficult to see what you have from that layout. Attach your workbook - the FAQ describes how to.

Pete

Hi Pete, thank you for the reply.
Here is the file test v.xlsx

I just added few data, but I will have to create a dynamic graph, linked to this table which will also be dynamic thanks to 2 lists: Regions and Year

Hope it's clearer now...

I presume in your real data you would have rows for Region 1 Actual, Region 1 Forecast, Region 1 Budget for 2013 and for other years, as well as Region 2 Actual, Region 2 Forecast, and so on?

If so, I'll make up some data along those lines so that you can see the effect better.

Are you intending to use drop-downs in cells B8 and B9 to select the region and year?

Pete