First you have to define a SheetList range name (the range of sheet names being the sources of SUMIFS). You can input the name of the sheets in a separate sheet of your excel file. Select all the cells containing this sheet names, then go to the name box field (the upper left field where you see the address of the cells usually – as seen in the picture) and write SheetList.

After this just use the following formula, where

INDIRECT (“‘” & SheetList & “‘!$E:$E”) is the amounts column and

INDIRECT (“‘” & SheetList & “‘!$F:$F”) is the criteria column.

Don’t forget the SUMPRODUCT, it doesn’t work without it.

=SUMPRODUCT( SUMIFS( INDIRECT(“‘” & SheetList & “‘!$E:$E”) , INDIRECT(“‘” & SheetList & “‘!$F:$F”),A5)

Have fun with Excel!