R
RS
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),COUNTIF(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).
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),COUNTIF(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).