Excel 2007 : SUMIF and filtering

I have a spreadsheet with 2 worksheets. Sheet 1 is the summary, and has my categories and my sumif formula. Sheet 2 is my data sheet. It has date (column a), category (column e) and amount (column g).

On my summary sheet in the B column, i use "=SUMIF(Data!E2:E2000,B3,Data!G2:G2000)" which is referencing column b which is my category section.

What I would like to do is have the SUMIF formula change when I change the filter on the data sheet. Currently it totals all the rows even if I filter it from all years, to just 1 year the total is the same. What should I do to allow the formula to be more dynamic so if I select 2010 in the data sheet, column a, that the summary sheet shows the totals for only that years worth of data?

I hope I am explaining this correctly. Any help is appreciated.

Thanks Mike

Hello Mike, try like this

=SUMPRODUCT((Data!E2:E2000=B3)+0,SUBTOTAL(9,OFFSET(Data!G2,ROW(Data!G2:G2000)-ROW(Data!G2),0)))