3D Sum using Indirect Function

Hi Excel gurus.

I want to make spreadsheet that will sum different sheets. But the number and names of these sheets should change dependant on dropdown list.

I can use 3d sum such as =SUM('1:4'!A1:A15) (1 and 4 are sheet names) also I can use Indirect function such as SUM(INDIRECT("'"&A6&"'!a1:a15"))

But what I want is combination of both of these functions. I wanna sum particular ranges in different sheets which user will have a chance change its names. Used that function but getting REF error. SUM(INDIRECT("'"&A2&":"&B2&"'!a1:a15"))

Illustrated my problem in excel file and tried to to as clear as I could be.

I appreciate any help coming from you.

Thanx in advance

Try this formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT(A2&":"&B2))&"'!A1:A15"),">0"))

assumes all positive values in your ranges

Originally Posted by daddylonglegs

Try this formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT(A2&":"&B2))&"'!A1:A15"),">0"))

assumes all positive values in your ranges

Thank you very much. It works perfectly.
Trying to understand your formula. Its wonderful
My last question: Is it possible to use your formula to the sheets that does not include numbers.

Originally Posted by AZ-XL

Is it possible to use your formula to the sheets that does not include numbers.

Not that version, it relies on having numeric values. If you use this version

=SUMPRODUCT(SUMIF(INDIRECT("'"&INDEX(A6:A11,MATCH(A2,A6:A11,0)):INDEX(A6:A11,MATCH(B2,A6:A11,0))&"'!A1:A15"),">0"))

Then the sheet names can be text values (or numbers) and it will use the sheets that are positionally in the list between A2 and B2 - assumes that A2 will be in the list before B2

I am speechless. Thank you again. You are genious