Dear Biff,

Thanks for the suggestion. I tried using the formula you suggested but it
was returning a zero value for all the results. I think it was because the
months in my table refer to another cell which is actually a date (custom
formated so as to display only the month). Where B$36 refers to a cell in
the Worksheet with the following formula: =DATE(YEAR($H$2)-1,7,1) and the
format is Custom (mmm). In my case, H2 = 6/30/07. Also, Worksheet!$AC14 is
the program name "Home".

This your formula as I tried it:
=SUMPRODUCT(--(TEXT(Worksheet!$J$49:$J$70,"mmm")=B$36),--(Worksheet!$K$49:$K$70=Worksheet!$AC14))


The other thing that I also noticed is 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
refered to in cell B$36.


"Biff" wrote:

See this screencap:

http://img261.imageshack.us/img261/3...productjn1.jpg

Enter the formula in N50 then copy across then down.

Biff

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