Dear hlpmelrn,

Glad to hear that you found a solution that worked for you. The solution
that Biff provided (see the post dated 10/19/2006 3:29 PM PST) had a number
of advantages for me. One, rather than me having to manually adjust the
formula for each month, I could simply copy and paste it across the entire
table and it would do so by itself.

Two, since my formula refers to a date in my spreadsheet, not only does it
exclude ranges not in the current fiscal year, but when the fiscal year
changes, it will account for that and automatically update the table without
me having to go in and manually adjust the formulas.

"hlpmelrn" wrote:



"RS" wrote:

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

RS I had to count base on a date range and found if I used a formula array by putting in my formula then pressing CRTL+SHFT+ENTER I got the correct count. This is the formula I used to count all dates between Oct 1 2006 and Oct 31 2006 finding the =DateValue(10/10/2006) you can plug in your own critera with ="Home" and date maybe this will help you out not sure just learning myself.
=SUM(IF('Local Annual Sales'!B4:B19=38991,IF('Local Annual
Sales'!B4:B19<=39022,1,0),0))