Sumproduct with date criteria in headers

I have a Data Table that shows different accounting values in the rows with each column representing a monthly sum. The headers of each column in are the form of months (i.e. Jan-16, Feb-16). I am trying to create a new table that will allow me to calculate the sums for each year and yet another that will calculate monthly averages.

The original data table is named "dMonthlyData" and looks like:

Measurement	16-Jan	16-Feb
Sales	2,000,000	3,000,000
Costs	1,500,000	1,750,000

The sumproduct formula I'm trying to use looks like: =SUMPRODUCT((dMonthlyData[#Data])*(dMonthlyData[Measurement]="Sales")*(YEAR(dMonthlyData[#Headers])=2016))

So, the criteria is to get the sum (or average) of all the data in a particular row that happen to be in the columns where the header has a 2016 date. Is this possible without doing a PivotTable?

1. It would help to see the workbook.

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

What is the reason you don't want to use a pivot table?

Well
YEAR("Jan-12)

It's being interpraited as January 12th 2016
Year 2016 is assumed because no year was provided by the string
Only 2 elements of the date were provided Jan and 12.
Jan is obviously January.
But what is the 12 meant to be? Is that the DAY or the YEAR ?
Excel essentially made the assumption that you meant the 12 to be the DAY.
So it filled in the remaining/missing piece of a complete date, the YEAR.
When no year is provided, current year is assumed.

YEAR("28-Jan-12")

Now all elements of a complete date are provided.
And with that format, it correclty assumes the number at the end of the string is the year.

Not sure why I picked 28 for the day, I suppose you could use any Day # from 1 to 28.