You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"SmilingSteve" wrote in message
...
Thanks so much gentlemen. Very informative. Excel is not intuitively
obvious in respect to 3d refereces.

"T. Valko" wrote:

How does "01-september" become a serial number equal to september?

That's a "quirk" of Excel. Excel is very eager to indentify dates and
times.

MONTH("01-Septemnber")

That is clearly not a date serial number, it's a text string, but in its
eagerness to indentify dates/times Excel will "accept" that text string
in
certain DATE related functions and evaluate it as a legitimate date
string.
In the above, since the year number has been left out it defaults to the
current year. So:

MONTH("01-Septemnber")

Evaluates to the date September 1 2008 which is month number 9.

The inner indirect function reduces to "1:9" which becomes $1:$9. Why
does
this happen

It starts out as a text string within INDIRECT as "1:9". The INDIRECT
function converts TEXT representations of references to usable references
that can be passed to other functions. In this case we use INDIRECT to
"build" a text representation of the reference "1:9" which is in turn
passed to the ROW function which evaluates it as ROW($1:$9).

ROW($1:$9) is then passed to the TEXT function as an array that is
comprised
of:

{1,2,3,4,5,6,7,8,9}

Each element of that array is then multiplied by 28:

1*28 = 28
2*28 = 56
3*28 = 84
etc
etc
9*28 = 252

These multiplied values are evaluated as *date serial numbers for the
year
of 1900*. We're using the TEXT function to return the month names based
on
these date serial numbers:

Date serial number:
28 = January 28 1900
56 = February 25 1900
84 = March 24 1900
etc
etc
252 = Septemeber 8 1900

So the TEXT function evalautes those date serial numbers and returns the
name of the month as a text string. These month names are then
concatenated
along with the text string "!B9" and passed to the outer INDIRECT
function
as an array:

January!B9
February!B9
March!B9
etc
etc
September!B9

This array of text references is then converted by the outer INDIRECT
function into usable references that are in turn passed to the SUMIF
function.

Why do you use the sumif function and the sumproduct function?

Excel doesn't handle "3D" references very well. In fact, very few
functions
can work with 3D references and those that do have a very strict syntax.
So,
we use the combination of SUMPRODUCT and SUMIF to "trick" Excel into
calculating a "3D" formula.

In essence, what's happening with this formula is that it's calculating
an
array of SUMIF functions in which we had to use all of the INDIRECTS to
"build" the references.

=SUMPRODUCT
(
SUMIF(January!B9,"<1E100")
SUMIF(February!B9,"<1E100")
SUMIF(March!B9,"<1E100")
SUMIF(April!B9,"<1E100")
SUMIF(May!B9,"<1E100")
SUMIF(June!B9,"<1E100")
SUMIF(July!B9,"<1E100")
SUMIF(August!B9,"<1E100")
SUMIF(September!B9,"<1E100")
)

We use SUMPRODUCT because it can process arrays without having to be
array
entered (CTRL, SHIFT, ENTER).


--
Biff
Microsoft Excel MVP


"SmilingSteve" wrote in message
...
Biff
I have reviewed you equation and I have some questions.

the month function reduces to "01-september" and returns a value of 9.
How
does "01-september" become a serial number equal to september?

The inner indirect function reduces to "1:9" which becomes $1:$9. Why
does
this happen?

The rows function reduces to 9 which I think I understand.

The text function reduces to 252,"mmmm" which returns september. Why?

The outer indirect function reduces to "september!B9". Why do you use
the
sumif function and the sumproduct function?

Thanks

"T. Valko" wrote:

If you'd like, I can put together a sample file that demonstrates both
methods I suggested.


--
Biff
Microsoft Excel MVP


"SmilingSteve" wrote in
message
...
Biff
Thanks for your input. I am not intimately familiar with a lot of
excel
formula so I will need time to understand what you suggest. I will
get
back
to you.

Is there a good reference for how excel processes its functions?
Help
gives
the format and explanations of variables but not the how and why it
works.

"T. Valko" wrote:

Ooops!

Forgot something:

Enter this formula in J1:
Enter this formula in K1:

Now, select both J1 and K1 and copy down to J12:K12


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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?