DLL was referring to the W*1 in '[Weekly Sheets.xlsx]W1'!$D$15:$D$199="W*1"

If you're just looking for W, then

=SUMPRODUCT( ('[Weekly Sheets.xlsx]W1'!$B$15:$B$199=C1) * ('[Weekly Sheets.xlsx]W1'!$D$15:$D$199="W") )