Excel - CountIfs using column header and value from other column as criteria?

Question

I am trying to produce a countifs formula to put in a cell on sheet 1, that will count the number of entries for ID 'A' on sheet 2.

But also i need to add a criteria to say only count the entries where the date in the column header on sheet 2 matches the date in the column header on sheet 1.

Sheet1

ID        01/01/2016          
A             1 (Formula goes here)               
B             0 (Formula goes here)             
C             1 (Formula goes here)             
D             1 (Formula goes here)             


Sheet2
ID        01/01/2016      
A         1pm - 2pm       
B                         
C         1pm - 2pm        
D         1pm - 2pm                    

I am really struggling to get this to work. so far i have put together this, but it doesn't have the criteria for the dates.

=SUMPRODUCT((Data!$A$2:$A$1000=$A2)*(Data!$D$2:D$1000="$D$1"))

Please can somemone show me where i am going wrong?

Answer 1

Try something closer to,

=SUMPRODUCT(($F$2:$K$9<>"")*($F$1:$K$1=$B$1)*($E$2:$E$9=$A2))

I've put everything on one worksheet but you should have no trouble splitting it off to Sheet2.