convert Sumifs to sumproduct or similar

I would change your dates in rows 8 and 9 as having TEXT dates is not good practice

in G8

=DATE($D$4,COLUMN(A$1),1)

in G9

=EOMONTH(G$8,0)

copy both across

Formulas are going to further complicated as you need to use INDIRECT to reference the "Variable" sheets.

in G10 [as example]

=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))