Stevenson asks: Why won’t this SUMIFS work inside of a LET function in Excel?
It turns out that he is trying to do a calculation in the first argument of the SUMIFS.
This is against the rules.
If you would have tried it without the LET, Excel would have given you the mysterious error of “There’s a Problem With This Formula”.
When you try to do it inside of LET, the formula parser is not smart enough to refuse the formula, but you end up with a bunch of #VALUE! errors in Excel.
The same limitation applies to SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS, SUMIF, AVERAGEIF, DSUM, DCOUNT, DAVERAGE and so on.
The workaround is to switch to a FILTER function inside of the LET or simply a FILTER function inside of a SUM.

Table of Contents
(0:00) SUMIFS inside of LET
(1:13) Any calc as first LET argument
(1:47) Move calculation to grid
(2:12) Using FILTER instead