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

I am trying something like below-

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

CodePudding user response：

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)))

CodePudding user response：

For SUMIFS, you would need a different set-up using wildcards:

=SUMIFS(Sheet1!D:D,Sheet1!A:A,LEFT(Sheet3!B1,6)&"*")

though note that this will not work if the entries in Sheet1!A:A are numeric.