Sumif across multiple Sheets, except the SUMPRODUCT way is not working

Question

I have a list of Sheet names in A71:A99 and I need to Sum based on Account Codes. This formula is not working and returning REF errors.

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$71:$A$99&"'!C:C"),Consolidated!$B12,INDIRECT("'"&$A$71:$A$99&"'!A:A")))

Picture of problem

Based on the picture where am I going wrong?

Answer 1

Well, this works for me:

This is the formula on the sheet where I want the list of sheets:

TRANSPOSE(TEXTSPLIT(TEXTJOIN(",", FALSE,Firstsheet:Lastsheet!A1),","))

Note Scott Craner's suggestion for a better version:

TOCOL(Firstsheet:Lastsheet!A1,3)

This is the formula in cell A1 on each sheet, I have a sheet named Firstsheet and another Lastsheet so I have all the target sheets between those.

That way I can control the list as needed.

MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,50)