This now works on filter using XLent approach: and I would not worry to much about INDIRECT and performance for a few sheets!

Formula: copy to clipboard 
=SUMPRODUCT((INDIRECT("'Project Projections "&$D$4&"'!$E$8:$E$28"))*(INDIRECT("'Project Projections "&$D$4&"'!$B$8:$B$28")>='Stock Requirement'!G$8)*(INDIRECT("'Project Projections " & $D$4 &"'!$C$8:$C$28")<='Stock Requirement'!G$9)*(INDIRECT("'Project Projections " & $D$4 &"'!$D$8:$D$28")='Stock Requirement'!$C10)*(INDIRECT("'Project Projections " & $D$4 &"'!$D$8:$D$28")='Stock Requirement'!$C10)*(INDIRECT("'Project Projections " & $D$4 &"'!$F$8:$F$28")=1))