SUMIFS duplicate criteria range query

Hi,
I'm trying to create a file whose data changes depending on the drop down selected. This isn't a problem until I come to create a subtotal where I want to sumif or vlookup multiple criteria. I assumed that sumifs would be the answer but it seems to not work properly if I select the same criteria range repeatedly....
I've attached a simple example of what I mean.

The problem I have is that I am linking my excel file into a huge central excel file to pull out specific information and I am unable to amend the source data (it would be an easy fix if I could do this). I also do not want to create multiple worksheets and then total them up as this will make my file quite large itself - I'm essentially trying to create a reporting summary which can be used as a template and be adapted for other areas of the business.

Can anyone help with the sumifs or is there a better way of subtotalling without amending the base data or creating named ranges?

Thanks,

Wes

Try:

=IF(H2="Winter",SUMPRODUCT((SUMIF(A2:A13,E2:E5,B2:B13))),SUMIF(A2:A13,H2,B2:B13))

If you want to include, Spring, Summer and Autumn to your drop down list, then you can use:

Please Login or Register  to view this content.

Edit:

Forgot to mention that you need to fill in the cells in D2:D13 with associated seasons...and they must be grouped together.