STUCK** SUMIF INDEX MATCH - formula output sum of date range or month sum

[Antprod]

Hi, Thank you first of for reading and addressing.

I have a spreadsheet with daily oil production data from 2013-2015 (sheet name: "2013-2015 PAS Data"). In another sheet ("Yr-Month Avg.-***.") I am trying to use Index match to look up all the values associated with a particular heading and sum (or average) the values for that month.

So for example. I have total produced oil recorded every day from 1/01/2013 to 25/11/2015 in column J (and dates in column A), I want to create a dynamic table that will sum the total daily oil production for each month of 2013, 2014 so on. I have numerous headings that require a sum of the data and other functions down the track

my current formula looks like:

=SUMIFS(INDEX('2013-15 PAS Data'!$A$7:$AS$1000,,MATCH('Yr-Month Avg.-***'!G5,'2013-15 PAS Data'!$A$7:$AS$7)),'2013-15 PAS Data'!A:A, ">="&D6,'2013-15 PAS Data'!A:A, "<="&E6)

Where D6 and E6 are the start and end of reference month and G5 is the column heading "Total Produced Oil" which is the same column heading in the lookup sheet. I am getting an #VALUE! error at the moment

I have attached a sample version to assist with the explanation

I have quite a few headings that i need to find the monthly sum and monthly daily average from.
If you know of a more efficient way, please let me know.
NOTE: this is how my data is mostly exported and don't think it is easily transposable into a pivot table (??). but please let me know if there is a better way.

[JohnTopley]

For total oil production ..

=SUMIFS('2013-15 PAS Data'!$AF8:$AF1000,'2013-15 PAS Data'!A8:A1000, ">="&$D6,'2013-15 PAS Data'!A8:A1000, "<="&$E6)

You should be able to adjust this for other totals by changing the SUM range i.e AF in the above.