G7=September

Assuming you will *always* want the sum from January to ???

Try this:

=SUMPRODUCT(SUMIF(INDIRECT(TEXT(ROW(INDIRECT("1:"& MONTH("01-"&G7)))*28,"mmmm")&"!B9"),"<1E100"))

If that's too "obfuscated" then:

Enter this formula in J1:

=TEXT(ROWS(J$1:J1)*28,"mmmm")

Enter this formula in K1:

=INDIRECT(J1&"!B9")

Then your sum formula would be:

=SUM(K1:INDEX(K1:K12,MATCH(G7,J1:J12,0)))



--
Biff
Microsoft Excel MVP


"SmilingSteve" wrote in message
...
I have an excel workbook with multiple work sheets (Summary, January,
february..., December) On the Summary worksheet I want to create a 3d
reference summing all B9 cells across the monthly worksheets. I can do
this
for a specified range of months, like =sum(january:December!B9. I want
the
second reference, December, to be variable, like february or september.

I tried indirect but it evaluates the string left to right and
consequently
gives me a name error.

Any suggestions as to how I do this?