How do I count frequency based on 2 criteria (including month)

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

 

[Biff]

See this screencap:

http://img261.imageshack.us/img261/3509/sumproductjn1.jpg

Enter the formula in N50 then copy across then down.

Biff

 

[Trevor Shuttleworth]

=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

 

[Ron Coderre]

Would you consider letting a Pivot Table do the heavy lifting?

Try this:

<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the ClosingDate field here
COLUMN: Drag the ProgramType field here
DATA: Drag the ProgramType field here
If it doesn't list as Count of ProgramType...dbl-click it and set it to Count
(also...you can rename "Count of ProgramType" to something more appropriate)
Click [OK]
Select where you want the Pivot Table...and Click the [Finish] button

(Note: I'm having you temporarily put the ClosingDates on the left in case
there are more than 256...Excel's column limit.)

That will list ProgramTypes across the top
ClosingDates down the left
and the count of ProgramTypes.
(not quite there yet...but continue reading)

Right-click on the ClosingDate field
Select: Group and Show Detail
Group by: Months
Click [OK]

Now the left column displays Jan, Feb, Mar...etc...instead of dates.

After they're grouped, you can drag the ClosingDate field to the top
and drag the ProgramType to the left

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

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

 

[RS]

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.

See this screencap:

http://img261.imageshack.us/img261/3509/sumproductjn1.jpg

Enter the formula in N50 then copy across then down.

Biff

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

 

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

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

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.

See this screencap:

http://img261.imageshack.us/img261/3509/sumproductjn1.jpg

Enter the formula in N50 then copy across then down.

Biff

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

 

[RS]

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)*(Worksheet!$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.

=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

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

 

[RS]

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!!

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

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.

See this screencap:

http://img261.imageshack.us/img261/3509/sumproductjn1.jpg

Enter the formula in N50 then copy across then down.

Biff

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

 

[RS]

Dear Trevor,

Hopefully you will see this reply. Biff provided a solution to my
problem, but since Biff's answer uses a slightly different formula than
yours, and in the interest of broadening my Excel skills, how would you
change your formula to answer the issues in my previous reply (namely
including the year in the formula).

Also, in my original post I asked "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?].

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)*(Worksheet!$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.

=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

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

 

[RS]

Dear Ron,

Sorry for the delay in this reply. Although Biff has provided an
answer to my question, I wanted to go ahead and try to use the PivotTable
feature you were talking about (I've never used it before). I followed the
steps you outlined, although I had to make a modification by copying the
program types from cells AC14-AC23 to an adjacent column next to the
data-containing table in my Worksheet.

Having done this, I followed your instructions but couldn't continue
past the following steps:
Right-click on the ClosingDate field
Select: Group and Show Detail
Group by: Months
Click [OK]
Now the left column displays Jan, Feb, Mar...etc...instead of
dates.

I'm using Excel 2000 so this might account for some of the differences.
When I right-click on the ClosingDate field, I have a "Group and Outline"
option which expands to include 4 options (summarized as either Hide/Show
Detail and Group/Ungroup). When I choose "Show Detail", it gives me a list
of choices, and selecting a month simply displays that month and all the info
for that month. Repeating this to show multiple months and then trying to
highlight the months and select "Group" didn't work.

If I choose "Group" I get an error message saying "Cannot group that
selection." How do I fix this? Also, I noticed at the top of page (Row 1)
there is a blue outlined box going the length of the table that says "Drop
Page Fields Here." What does this do?

Would you consider letting a Pivot Table do the heavy lifting?

Try this:

<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the ClosingDate field here
COLUMN: Drag the ProgramType field here
DATA: Drag the ProgramType field here
If it doesn't list as Count of ProgramType...dbl-click it and set it to Count
(also...you can rename "Count of ProgramType" to something more appropriate)
Click [OK]
Select where you want the Pivot Table...and Click the [Finish] button

(Note: I'm having you temporarily put the ClosingDates on the left in case
there are more than 256...Excel's column limit.)

That will list ProgramTypes across the top
ClosingDates down the left
and the count of ProgramTypes.
(not quite there yet...but continue reading)

Right-click on the ClosingDate field
Select: Group and Show Detail
Group by: Months
Click [OK]

Now the left column displays Jan, Feb, Mar...etc...instead of dates.

After they're grouped, you can drag the ClosingDate field to the top
and drag the ProgramType to the left

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

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

 

[Roger Govier]

Hi

If you are having problems with Grouping your Dates by Month, it either
means that some cells within the range are not true Excel dates, or are
blank cells. The PT will not do the grouping unless every cell contains
a valid date.

If you have made the overall range in your original selection of data,
longer than that currently used to allow for more data being added, that
could be the problem. Limit the range to just the used area or create a
Dynamic range for your data (for help on this see below)

The Page area, is one of 3 different areas to which you can allocate
fields (columns) from your raw data.

For more help on setting up and using Pivot tables go to Debra
Dalgleish's site
http://www.contextures.com/tiptech.html
and scroll to the section on Pivot Tables
and for Dynamic Range naming
http://www.contextures.com/xlNames01.html#Dynamic

or Mike Alexander's site
http://www.datapigtechnologies.com/ExcelMain.htm
http://www.datapigtechnologies.com/flashfiles/pivot1.html

Do persist with getting to grips with Pivot Tables; you will find them
invaluable.

--
Regards

Roger Govier

Would you consider letting a Pivot Table do the heavy lifting?

Try this:

<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the ClosingDate field here
COLUMN: Drag the ProgramType field here
DATA: Drag the ProgramType field here
If it doesn't list as Count of ProgramType...dbl-click it and set it
to Count
(also...you can rename "Count of ProgramType" to something more
appropriate)
Click [OK]
Select where you want the Pivot Table...and Click the [Finish] button

(Note: I'm having you temporarily put the ClosingDates on the left in
case
there are more than 256...Excel's column limit.)

That will list ProgramTypes across the top
ClosingDates down the left
and the count of ProgramTypes.
(not quite there yet...but continue reading)

Right-click on the ClosingDate field
Select: Group and Show Detail
Group by: Months
Click [OK]

Now the left column displays Jan, Feb, Mar...etc...instead of dates.

After they're grouped, you can drag the ClosingDate field to the top
and drag the ProgramType to the left

To refresh the Pivot Table, just right click it and select Refresh
Data

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

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

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

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!!

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

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.


:

See this screencap:

http://img261.imageshack.us/img261/3509/sumproductjn1.jpg

Enter the formula in N50 then copy across then down.

Biff

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

 

[RS]

Dear Roger,

Thanks for your response. You're right in that my client has some
blank cells in the closing date column hence I can't group the cells by
month, although the PivotTable does generate a row at the bottom called
(blanks).

I also just quickly checked the links which you provided and found them
to be incredibly useful. Coincidentally (& for the first time), I had found
a solution to a autofiltering and password protection problem just a couple
of days ago from Debra Dalgleish's Contextures website. She provided a
solution that many other posts in different forums (Google groups & even
Microsoft's official help topic on this issue!) didn't solve.

Also, the link to Mike Alexander's DataPig Excel Training site is
absolutely incredible! I checked out his info on PivotTable grouping and
when I clicked on his link, there was a Flash video tutorial explaining
exactly how to do that!! As much as I don't mind reading instructions on how
to do something, I feel that watching a video of what needs to be done is
much more effective and SO MUCH QUICKER than reading those same instructions.
Thank you so much for your assistance (ESPECIALLY the 2 links that you
provided)!!!!!!

Hi

If you are having problems with Grouping your Dates by Month, it either
means that some cells within the range are not true Excel dates, or are
blank cells. The PT will not do the grouping unless every cell contains
a valid date.

If you have made the overall range in your original selection of data,
longer than that currently used to allow for more data being added, that
could be the problem. Limit the range to just the used area or create a
Dynamic range for your data (for help on this see below)

The Page area, is one of 3 different areas to which you can allocate
fields (columns) from your raw data.

For more help on setting up and using Pivot tables go to Debra
Dalgleish's site
http://www.contextures.com/tiptech.html
and scroll to the section on Pivot Tables
and for Dynamic Range naming
http://www.contextures.com/xlNames01.html#Dynamic

or Mike Alexander's site
http://www.datapigtechnologies.com/ExcelMain.htm
http://www.datapigtechnologies.com/flashfiles/pivot1.html

Do persist with getting to grips with Pivot Tables; you will find them
invaluable.

--
Regards

Roger Govier

Would you consider letting a Pivot Table do the heavy lifting?

Try this:

<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the ClosingDate field here
COLUMN: Drag the ProgramType field here
DATA: Drag the ProgramType field here
If it doesn't list as Count of ProgramType...dbl-click it and set it
to Count
(also...you can rename "Count of ProgramType" to something more
appropriate)
Click [OK]
Select where you want the Pivot Table...and Click the [Finish] button

(Note: I'm having you temporarily put the ClosingDates on the left in
case
there are more than 256...Excel's column limit.)

That will list ProgramTypes across the top
ClosingDates down the left
and the count of ProgramTypes.
(not quite there yet...but continue reading)

Right-click on the ClosingDate field
Select: Group and Show Detail
Group by: Months
Click [OK]

Now the left column displays Jan, Feb, Mar...etc...instead of dates.

After they're grouped, you can drag the ClosingDate field to the top
and drag the ProgramType to the left

To refresh the Pivot Table, just right click it and select Refresh
Data

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

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

 

[Trevor Shuttleworth]

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

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)*(Worksheet!$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.

=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

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

 

[Roger Govier]

Hi

Glad you found the links useful. All the thanks though is really due to
them for providing us all with such valuable resource.

I usually get around the problem of blank dates by making the blanks a
date way into the future, rather than blank.
Then, you can use Grouping by Date, but set the range of dates to
Exclude the far distant dates.

--
Regards

Roger Govier

Dear Roger,

Thanks for your response. You're right in that my client has some
blank cells in the closing date column hence I can't group the cells
by
month, although the PivotTable does generate a row at the bottom
called
(blanks).

I also just quickly checked the links which you provided and found
them
to be incredibly useful. Coincidentally (& for the first time), I had
found
a solution to a autofiltering and password protection problem just a
couple
of days ago from Debra Dalgleish's Contextures website. She provided
a
solution that many other posts in different forums (Google groups &
even
Microsoft's official help topic on this issue!) didn't solve.

Also, the link to Mike Alexander's DataPig Excel Training site is
absolutely incredible! I checked out his info on PivotTable grouping
and
when I clicked on his link, there was a Flash video tutorial
explaining
exactly how to do that!! As much as I don't mind reading instructions
on how
to do something, I feel that watching a video of what needs to be done
is
much more effective and SO MUCH QUICKER than reading those same
instructions.
Thank you so much for your assistance (ESPECIALLY the 2 links that you
provided)!!!!!!

Hi

If you are having problems with Grouping your Dates by Month, it
either
means that some cells within the range are not true Excel dates, or
are
blank cells. The PT will not do the grouping unless every cell
contains
a valid date.

If you have made the overall range in your original selection of
data,
longer than that currently used to allow for more data being added,
that
could be the problem. Limit the range to just the used area or create
a
Dynamic range for your data (for help on this see below)

The Page area, is one of 3 different areas to which you can allocate
fields (columns) from your raw data.

For more help on setting up and using Pivot tables go to Debra
Dalgleish's site
http://www.contextures.com/tiptech.html
and scroll to the section on Pivot Tables
and for Dynamic Range naming
http://www.contextures.com/xlNames01.html#Dynamic

or Mike Alexander's site
http://www.datapigtechnologies.com/ExcelMain.htm
http://www.datapigtechnologies.com/flashfiles/pivot1.html

Do persist with getting to grips with Pivot Tables; you will find
them
invaluable.

--
Regards

Roger Govier

Would you consider letting a Pivot Table do the heavy lifting?

Try this:

<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the ClosingDate field here
COLUMN: Drag the ProgramType field here
DATA: Drag the ProgramType field here
If it doesn't list as Count of ProgramType...dbl-click it and set
it
to Count
(also...you can rename "Count of ProgramType" to something more
appropriate)
Click [OK]
Select where you want the Pivot Table...and Click the [Finish]
button

(Note: I'm having you temporarily put the ClosingDates on the left
in
case
there are more than 256...Excel's column limit.)

That will list ProgramTypes across the top
ClosingDates down the left
and the count of ProgramTypes.
(not quite there yet...but continue reading)

Right-click on the ClosingDate field
Select: Group and Show Detail
Group by: Months
Click [OK]

Now the left column displays Jan, Feb, Mar...etc...instead of
dates.

After they're grouped, you can drag the ClosingDate field to the
top
and drag the ProgramType to the left

To refresh the Pivot Table, just right click it and select Refresh
Data

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

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

 

[RS]

Dear Trevor,

Thanks for the additional response!

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

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)*(Worksheet!$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.

=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


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

 

[RS]

Dear Roger,

Although the blank tip you provided wouldn't be possible for my client
to use, it is a useful tip that I could use sometime in the future as I learn
more about pivot tables. Thanks again for those links.

Hi

Glad you found the links useful. All the thanks though is really due to
them for providing us all with such valuable resource.

I usually get around the problem of blank dates by making the blanks a
date way into the future, rather than blank.
Then, you can use Grouping by Date, but set the range of dates to
Exclude the far distant dates.

--
Regards

Roger Govier

Dear Roger,

Thanks for your response. You're right in that my client has some
blank cells in the closing date column hence I can't group the cells
by
month, although the PivotTable does generate a row at the bottom
called
(blanks).

I also just quickly checked the links which you provided and found
them
to be incredibly useful. Coincidentally (& for the first time), I had
found
a solution to a autofiltering and password protection problem just a
couple
of days ago from Debra Dalgleish's Contextures website. She provided
a
solution that many other posts in different forums (Google groups &
even
Microsoft's official help topic on this issue!) didn't solve.

Also, the link to Mike Alexander's DataPig Excel Training site is
absolutely incredible! I checked out his info on PivotTable grouping
and
when I clicked on his link, there was a Flash video tutorial
explaining
exactly how to do that!! As much as I don't mind reading instructions
on how
to do something, I feel that watching a video of what needs to be done
is
much more effective and SO MUCH QUICKER than reading those same
instructions.
Thank you so much for your assistance (ESPECIALLY the 2 links that you
provided)!!!!!!

Hi

If you are having problems with Grouping your Dates by Month, it
either
means that some cells within the range are not true Excel dates, or
are
blank cells. The PT will not do the grouping unless every cell
contains
a valid date.

If you have made the overall range in your original selection of
data,
longer than that currently used to allow for more data being added,
that
could be the problem. Limit the range to just the used area or create
a
Dynamic range for your data (for help on this see below)

The Page area, is one of 3 different areas to which you can allocate
fields (columns) from your raw data.

For more help on setting up and using Pivot tables go to Debra
Dalgleish's site
http://www.contextures.com/tiptech.html
and scroll to the section on Pivot Tables
and for Dynamic Range naming
http://www.contextures.com/xlNames01.html#Dynamic

or Mike Alexander's site
http://www.datapigtechnologies.com/ExcelMain.htm
http://www.datapigtechnologies.com/flashfiles/pivot1.html

Do persist with getting to grips with Pivot Tables; you will find
them
invaluable.

--
Regards

Roger Govier


Would you consider letting a Pivot Table do the heavy lifting?

Try this:

<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW: Drag the ClosingDate field here
COLUMN: Drag the ProgramType field here
DATA: Drag the ProgramType field here
If it doesn't list as Count of ProgramType...dbl-click it and set
it
to Count
(also...you can rename "Count of ProgramType" to something more
appropriate)
Click [OK]
Select where you want the Pivot Table...and Click the [Finish]
button

(Note: I'm having you temporarily put the ClosingDates on the left
in
case
there are more than 256...Excel's column limit.)

That will list ProgramTypes across the top
ClosingDates down the left
and the count of ProgramTypes.
(not quite there yet...but continue reading)

Right-click on the ClosingDate field
Select: Group and Show Detail
Group by: Months
Click [OK]

Now the left column displays Jan, Feb, Mar...etc...instead of
dates.

After they're grouped, you can drag the ClosingDate field to the
top
and drag the ProgramType to the left

To refresh the Pivot Table, just right click it and select Refresh
Data

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


:

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

 

[hlpmelrn]

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

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

 

[RS]

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.

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

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