Formula in criteria range in SUMIFS

Question

Can I apply the formula in the criteria range in SUMIFS as I don't want to do it outside?

I am tried something like this:

=SUMIFS('sheet1'!D:D,(LEFT('sheet2'!A:A,6)),"="&LEFT('sheet3'!B1,6))

Answer 1

No, that kind of manipulation doesn't work in SumIfs. You would need to use SumProduct. You shouldn't use full-column references with that, since that will be very slow.

=SUMProduct('sheet1'!$D$1:$D$1000,--(LEFT('sheet2'!$A$1:$A$1000,6)=LEFT('sheet3'!B1,6)))