This is a copy of what's stated  for the MONTH function in Excel help:

"Returns the month of a date represented by a serial number. The month is
given as an integer, ranging from 1 (January) to 12 (December)."

If you enter a date such as 01-September, Excel converts that to a date
serial number of 39692 which is September 1, 2008. Thus the MONTH function
applied to that date returns 9 as September is the 9th month of the year. As
for the TEXT function returning September, the 252 is the date serial number
for September 8, 1900 and when that is formatted as "mmmm", it becomes
September. As for your other questions, a good book on Excel such as Excel
2003 Bible or Excel 2007 Bible by John Walkenbach would help you.

Tyro

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.

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.

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).