Redefine your sheet1 names to cover only the numeric portion of the
ranges - exclude the headers. Then enter the following in B2 of
sheet2, assuming your headers are in row 1 and left labels in col A:

=SUMPRODUCT((Loc=$A2)*(Dept=B$1)*Quant)

Copy across and down.

HTH