You're welcome. Thanks for the feedback!

Biff

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

Sorry for the delay in my response. Since I'm new at using this
forum, I didn't know if answering yes would close this thread and not
allow
me to respond to the other posts (hopefully it won't after this reply).

Anyway...the update you provided to your formula works like a charm!!
I tested it by changing a closing date for one of the "Home" programs from
7/31/06 to 7/31/07 (which would be in the next fiscal year) and the
formula
removed it's occurrence from the current fiscal year's table. Once again,
thank you so much for your quick solution to my problem!!

"Biff" wrote:

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

Yep, that's a problem!

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.

Yep, that's another problem!

Try this:

=SUMPRODUCT(--(TEXT(Worksheet!$J$49:$J$70,"mmmyyyy")=TEXT(B$36," mmmyyyy")),--(Worksheet!$K$49:$K$70=Worksheet!$AC14))

Biff

"RS" wrote in message
...
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).