SumIFS w/ Index Match - Horizontal and Vertical

[Hunter Childress]

Please see the attached example. On the output tab, I am tying to sum by type (column) and by month(row). I can't get it to work.

 

[vletm]

Hunter Childress
eg to Output's cell B2 =SUMIF(Sheet1!$A$3:$A$71,Output!$A2,Sheet1!B$3:B$71)
and copy down & right as need.

 

[p45cal]

Try in B2:

=SUM(FILTER(FILTER(Sheet1!$B$3:$HW$71,Sheet1!$B$1:$HW$1=B$1),Sheet1!$A$3:$A$71=$A2))

copy down/across.

(Power Query could do this too.)

 

[Hunter Childress]

@p45cal - this is great, thanks for teaching me a new formula combo. This worked perfectly.

 

[Excel Wizard]

Please try

=MMULT(MMULT(N(TOROW(Sheet1!A3:A71)=A2:A4),Sheet1!B3:HW71),N(TOCOL(Sheet1!B1:HW1)=B1:K1))