Something like:

=SUMPRODUCT(($J$49:$J$73=DATE(2006,7,1))*($J$49:$ J$73<=DATE(2007,6,30))*(MONTH($J$49:$J$73)=7)*($K$ 49:$K$73=$AC14))

Regards

Trevor


"RS" wrote in message
...
Dear Trevor,

Sorry for the delay in my reply, but after spending 30 minutes
organizing
& composing my reply, when I hit the post button it deleted everything I
had
typed and asked me to sign back in. Anyway, thanks for your help. This
is
your formula as I tried it:
=SUMPRODUCT((MONTH(Worksheet!$J$49:$J$73)=7)*(Work sheet!$K$49:$K$73=Worksheet!$AC14))
where Worksheet!$AC14 is the program name "Home".

The formula worked, but one thing I noticed was that if the closing dates
are from a different fiscal year, the formula would also include those
months
also. So,
I guess I need to have a formula which takes into account the month and
year
also.

In my table on a separate worksheet called (FY07 Table), the column
headings are months that actually refer to cells in the data-containing
worksheet (called Worksheet). For example, the month of July (B36) in
this
summary table has the formula: =Worksheet!M5 where Worksheet!M5 is also
July
and has the following formula: =DATE(YEAR($H$2)-1,7,1) and the format is
Custom (mmm). In my case, H2 = 6/30/07.

Since my fiscal year starts runs from July 06 - June 07. The formula
for the months Jan 07 - Jun 07 follow the format: for Jan 07:
=DATE(YEAR($H$2)-1,13,1) and for Jun 07: =DATE(YEAR($H$2)-1,18,1). If
I'm
going to use years also, would I use 13-18 or 1-6 for the months? Thanks
for
your input.

"Trevor Shuttleworth" wrote:

=SUMPRODUCT((MONTH($J$49:$J$969)=7)*($K$49:$K$969= "home"))

or

=SUMPRODUCT(--(MONTH($J$49:$J$969)=7),--($K$49:$K$969="home"))

Regards

Trevor


"RS" wrote in message
...
Hi everyone. I've spent quite a few hours looking all over the
internet
and
within this community for the answer to my question. While there are
various
solutions out there [using SUMPRODUCT for example (which I've never
used)], I
can't seem to find one that specifically addresses my issue (I'm sure
it
must
be out there but I just can't find it).

Here's the situation my client wants: "...is it possible to track the
number of program types on a monthly basis?"

In the spreadsheet, there's a column with closing dates (data starts in
J49
on down; format for dates is m/d/y, example: 7/31/06) and another with
program types (starting in K49 on down; example: Home). The programs
types
come from a list of 10 choices located in cells AC14:AC23.

I'm trying to create a separate table on a different worksheet with
months
as the column headings and the 10 program types as the row headings.
Here
is
part of the new table:

Jul Aug Sep Oct Nov Dec
Home - - - - - -
Kin - - - - - -
FC - - - - - -
IFC - - - - - -
GH - - - - - -
IL - - - - - -
Res - - - - - -
Hosp - - - - - -
STARR - - - - - -
Other - - - - - -

I tried creating a formula to do this but I'm having some problems.
Here
is
what I tried for tracking "Home" in the month of July:
IF(MONTH(Worksheet!$J$49:Worksheet!$J$969,7),COUNT IF(Worksheet!$K$49:Worksheet!$K$969,Worksheet!$AC$ 14))

Even though there are currently only 20 or so rows filled in I used
J969 to make sure the formula went far enough down. I know that Excel
automatically extends formulas but I didn't know if it would also do it
for
calculations already in the spreadsheet [ex: would sum(M49:M69) or in
this
case (MONTH(J49:J69))be automatically extended to include additional
rows
of
data as they were added?].

I know that I would need to change the absolute reference from
$AC$14
to $AC14 when copying the forumlas down the table to include the other
programs.

I figure that rather than wasting any more hours (already have
spent
many hours) searching for a solution all over the web, I would post my
question to all the experts in this community. I'm sure someone with
much
greater expertise than I have should be able to solve my problem
fairly
easily. Thank you once again and sorry if this solution has been
answered
before (couldn't find it).