Summing Data with Multiple Criteria on Horizontal and Vertical Axis'

I have tried solving this as many ways as I can possibly think of, but nothing seems to be working.

I have attached a sample file below with simplified data to try and illustrate my issue. The data in the sample file shows data for multiple members under multiple categories for multiple months. The output on the Sample Summary Sheet shows the current months results and YTD results.

My logic said that the formula in F3 on the Summary sheet would have been =SUMIFS('Sample Data Sheet'!C3:N7,'Sample Data Sheet'!C1:N1,"*2015*",'Sample Data Sheet'!C2:N2,"Yes",'Sample Data Sheet'!A2:A7,A3) but this results in a #VALUE! error. I've tried sum and sumproduct and a variety of other formulas trying to figure out what the best way is to make the report work, but they all result in errors. I would take the clunky approach and sum VLOOKUP values, but each month the newest data will be added to columns C:E of the Data Sheet, followed by 12 months of moving data, so I would be adding new VLOOKUP ranges each month, and with 1200 members and four Sample Data Sheets, it would bog the file down too much.

Sample Data Sheet.xlsx

For the February 2015 values:

Formula:

=SUMPRODUCT(('Sample Data Sheet'!$A$3:$A$7='Sample Summary Sheet'!$A3)*('Sample Data Sheet'!$B$3:$B$7='Sample Summary Sheet'!$B3)*('Sample Data Sheet'!$C$2:$N$2='Sample Summary Sheet'!C$2)*('Sample Data Sheet'!$C$1:$N$1=DATE(2015,2,1))*('Sample Data Sheet'!$C$3:$N$7))

Regards, TMS

C3:

Formula:

=SUMPRODUCT(('Sample Data Sheet'!$A$3:$A$7=$A3)*('Sample Data Sheet'!$B$3:$B$7=$B3)*('Sample Data Sheet'!$C$2:$N$2=C$2)*('Sample Data Sheet'!$C$1:$N$1=DATE(2015,2,1))*('Sample Data Sheet'!$C$3:$N$7))

copied to February 2015 Yes and No cells

F3:

Formula:

=SUMPRODUCT(('Sample Data Sheet'!$A$3:$A$7=$A3)*('Sample Data Sheet'!$B$3:$B$7=$B3)*('Sample Data Sheet'!$C$2:$N$2=F$2)*(YEAR('Sample Data Sheet'!$C$1:$N$1)=2015)*('Sample Data Sheet'!$C$3:$N$7))

copied to 2015 Yes and No cells

E3:

Formula:

=C3/(C3+D3)

copied to all percentage cells.


Regards, TMS

You're welcome.