Search code examples
excelsumifs

formula in criteria range in SUMIFS

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

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