Creating a range within INDIRECT Function (help needed)

I am trying to update a function so that cell references A3 and A4 become range references. For example, I have 10 worksheets I want to sum cell F38 from. A3 would be Sheet 1 and A4 would be sheet 10. Right now it is only summing F38 from Sheet 1 and Sheet 10, and nothing in-between. Does anyone have a fix? See below for current formula.

This is also a formula where I would like to keep A3 and A4 to updatable references, for example I would easily be able to change the "values" within those cells so that I can expand the reference in the future.

=SUMPRODUCT(SUMIF(INDIRECT("'"&A3:A4&"'!f38"),"<>0"))

These formulae all produce the same result:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Note that you can add dummy Start and End worksheets that bracket the sheets you want to sum.

Can't help thinking that SUMIF(… , "<>0") is redundant … if a cell has a zero value, it won't affect the sum.

You do need to list all the sheets.

Your best bet, IMO, is to use the Start:End worksheets approach. They can, if you wish be hidden. Then all you need to do is include the sheets you want to sum in between the Start and End sheets. Equally, if you want to exclude any sheets, you can drag them outside the "bookends". You would not need to change any formulae or any "ranges".

I did try variations on your original formula but had no joy.