Excel 2007 : Add cells across worksheets using Indirect formula

[davidbauer]

I have a workbook which lists worksheets January through December. I would like to have a summary worksheet where I can enter any two months and receive the sum of the cells between those months. I was almost successful with:

=SUMPRODUCT(SUMIF(INDIRECT(""&$C$1:$C$2&"!"&P$1&$E9),"<>0"))

cell c1 being the first month in the formula, c2 being the second month, P1 referencing the Column Letter and E9 referencing the row number but the above formula seems to set stuck only adding cells from the first two months.

not sure if the above makes sense. any help would be appreciated.

If there is another way, other that indirect, i will accept the suggestions!

THANKS!

[DonkeyOte]

You can't use INDIRECT to build a 3D reference, eg

INDIRECT("'"&$C$1:$C$2&"!"&P$1&$E9)

where

C1: January
C2: December
P1: A
E9: 10

does not generate January:December!A10 3D reference.
Each sheet is processed separately and thus the conditional test is applied only to January!A10 and December!A10

In essence to do what you're trying with SUMPRODUCT, SUMIF & INDIRECT you need to generate a listing of the sheets to be included.
The list can only include valid sheet names.
Obviously you may resize the list so it's a good idea to use a Dynamic Named Range.

You might consider using a User Defined Function (VBA) as the above is neither efficient nor flexible (ie you want different calcs to use different sheet lists for ex.)

There is also the possibility of third party tools like morefunc.xll (THREED function) but that too is not necessarily viable depending on your other requirements (network usage, version etc...).