How to use ranges in formulas with external sheet links

Question

I'd like to use a SUMIF formula in one sheet that references a range in a separate sheet.

My problem is that using the range in this situation is only working for the first column of the range. So if my formula is:

=SUMIF('Data Referenced'!$A$2:$A,Formula!$A2,'Data Referenced'!$B$2:$M$11)

then it only adds as if I used the range B2:B. Using the same formula with internal ranges works fine as expected, so I'm not sure what I'm doing wrong.

I've laid out my example in the Google Doc linked below:

https://docs.google.com/spreadsheets/d/13zT2GlElgW5JkU90sOBeVlhr7FDOYeqaysDE-39fyao/edit?usp=sharing

Answer 1

use in B2:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A, QUERY({'Data Referenced'!A2:A11, 
 MMULT('Data Referenced'!B2:M11*1, 
 SEQUENCE(COLUMNS('Data Referenced'!B2:M11))^0)}, 
 "select Col1,sum(Col2) group by Col1"), 2, 0)))