Selecting date range for counts
 

I have two worksheets (example below). The first is a status report that
lists status, date reported, date closed. The second worksheet I have to
create is an Activity report. Each week, the activity report must show
1) a count of the total number of items in the status worksheet as of that
week
2) a count of how many new items were opened during the week
3) a count of how many items are in open status as of that week
4) a count of how many items were closed that week
5) a count of how many items are in closed status as of that week

What formulas can I use in the activity report to give me the counts of
these 5 items each week?

Thanking anyone who can help me:)

STATUS REPORT
status date opened date closed
open 2/4/2008
closed 2/4/2008 2/18/2008
closed 2/13/2008 3/15/2008
open 2/24/2008
pending 2/23/2008
pending 3/1/2008


ACTIVITY REPORT
activity week total new total closed total
items this wk open this wk
closed
2/3/2008 2/9/2008 2 2 2 0 0
2/10/2008 2/16/2008 3 1 3 0 0
2/17/2008 2/23/2008 3 0 2 1 1
2/24/2008 3/1/2008
3/2/2008 3/8/2008

Selecting date range for counts
 

Here's a start.

For Total Items try this:


=SUMPRODUCT(--($B$3:$B$8=A17),--(ISNUMBER($C$3:$C$8)),--($C$3:$C$8=B17))

B3:B8 is the start date
C3:C8 is the end date
A17 is the beginning of the date range to test
B17 is the end of the date range to test.

I don't have time to figure out the rest. This should get you started.
HTH,
Barb Reinhardt



"bokey" wrote:

I have two worksheets (example below). The first is a status report that
lists status, date reported, date closed. The second worksheet I have to
create is an Activity report. Each week, the activity report must show
1) a count of the total number of items in the status worksheet as of that
week
2) a count of how many new items were opened during the week
3) a count of how many items are in open status as of that week
4) a count of how many items were closed that week
5) a count of how many items are in closed status as of that week

What formulas can I use in the activity report to give me the counts of
these 5 items each week?

Thanking anyone who can help me:)

STATUS REPORT
status date opened date closed
open 2/4/2008
closed 2/4/2008 2/18/2008
closed 2/13/2008 3/15/2008
open 2/24/2008
pending 2/23/2008
pending 3/1/2008


ACTIVITY REPORT
activity week total new total closed total
items this wk open this wk
closed
2/3/2008 2/9/2008 2 2 2 0 0
2/10/2008 2/16/2008 3 1 3 0 0
2/17/2008 2/23/2008 3 0 2 1 1
2/24/2008 3/1/2008
3/2/2008 3/8/2008

Selecting date range for counts
 

I'm thinking my reply was for Open AND closed this week.
--
HTH,
Barb Reinhardt



"Barb Reinhardt" wrote:

Here's a start.

For Total Items try this:


=SUMPRODUCT(--($B$3:$B$8=A17),--(ISNUMBER($C$3:$C$8)),--($C$3:$C$8=B17))

B3:B8 is the start date
C3:C8 is the end date
A17 is the beginning of the date range to test
B17 is the end of the date range to test.

I don't have time to figure out the rest. This should get you started.
HTH,
Barb Reinhardt



"bokey" wrote:

I have two worksheets (example below). The first is a status report that
lists status, date reported, date closed. The second worksheet I have to
create is an Activity report. Each week, the activity report must show
1) a count of the total number of items in the status worksheet as of that
week
2) a count of how many new items were opened during the week
3) a count of how many items are in open status as of that week
4) a count of how many items were closed that week
5) a count of how many items are in closed status as of that week

What formulas can I use in the activity report to give me the counts of
these 5 items each week?

Thanking anyone who can help me:)

STATUS REPORT
status date opened date closed
open 2/4/2008
closed 2/4/2008 2/18/2008
closed 2/13/2008 3/15/2008
open 2/24/2008
pending 2/23/2008
pending 3/1/2008


ACTIVITY REPORT
activity week total new total closed total
items this wk open this wk
closed
2/3/2008 2/9/2008 2 2 2 0 0
2/10/2008 2/16/2008 3 1 3 0 0
2/17/2008 2/23/2008 3 0 2 1 1
2/24/2008 3/1/2008
3/2/2008 3/8/2008

Selecting date range for counts
 

Thanks for your response. I did figure out how to count the total number of
items by selecting a range and counting non-blank cells. The other 4 items
are more difficult. I'm trying to use the countif function but I haven't
been able to figure it out yet.

"Barb Reinhardt" wrote:

I'm thinking my reply was for Open AND closed this week.
--
HTH,
Barb Reinhardt



"Barb Reinhardt" wrote:

Here's a start.

For Total Items try this:


=SUMPRODUCT(--($B$3:$B$8=A17),--(ISNUMBER($C$3:$C$8)),--($C$3:$C$8=B17))

B3:B8 is the start date
C3:C8 is the end date
A17 is the beginning of the date range to test
B17 is the end of the date range to test.

I don't have time to figure out the rest. This should get you started.
HTH,
Barb Reinhardt



"bokey" wrote:

I have two worksheets (example below). The first is a status report that
lists status, date reported, date closed. The second worksheet I have to
create is an Activity report. Each week, the activity report must show
1) a count of the total number of items in the status worksheet as of that
week
2) a count of how many new items were opened during the week
3) a count of how many items are in open status as of that week
4) a count of how many items were closed that week
5) a count of how many items are in closed status as of that week

What formulas can I use in the activity report to give me the counts of
these 5 items each week?

Thanking anyone who can help me:)

STATUS REPORT
status date opened date closed
open 2/4/2008
closed 2/4/2008 2/18/2008
closed 2/13/2008 3/15/2008
open 2/24/2008
pending 2/23/2008
pending 3/1/2008


ACTIVITY REPORT
activity week total new total closed total
items this wk open this wk
closed
2/3/2008 2/9/2008 2 2 2 0 0
2/10/2008 2/16/2008 3 1 3 0 0
2/17/2008 2/23/2008 3 0 2 1 1
2/24/2008 3/1/2008
3/2/2008 3/8/2008

Selecting date range for counts
 

ForOpen this week try this:

=SUMPRODUCT(--($B$3:$B$8=A17),--($B$3:$b$8=B17))

For Closed This week try

=SUMPRODUCT(--($C$3:$C$8=A17),--($C$3:$C$8=B17))

--
HTH,
Barb Reinhardt



"bokey" wrote:

Thanks for your response. I did figure out how to count the total number of
items by selecting a range and counting non-blank cells. The other 4 items
are more difficult. I'm trying to use the countif function but I haven't
been able to figure it out yet.

"Barb Reinhardt" wrote:

I'm thinking my reply was for Open AND closed this week.
--
HTH,
Barb Reinhardt



"Barb Reinhardt" wrote:

Here's a start.

For Total Items try this:


=SUMPRODUCT(--($B$3:$B$8=A17),--(ISNUMBER($C$3:$C$8)),--($C$3:$C$8=B17))

B3:B8 is the start date
C3:C8 is the end date
A17 is the beginning of the date range to test
B17 is the end of the date range to test.

I don't have time to figure out the rest. This should get you started.
HTH,
Barb Reinhardt



"bokey" wrote:

I have two worksheets (example below). The first is a status report that
lists status, date reported, date closed. The second worksheet I have to
create is an Activity report. Each week, the activity report must show
1) a count of the total number of items in the status worksheet as of that
week
2) a count of how many new items were opened during the week
3) a count of how many items are in open status as of that week
4) a count of how many items were closed that week
5) a count of how many items are in closed status as of that week

What formulas can I use in the activity report to give me the counts of
these 5 items each week?

Thanking anyone who can help me:)

STATUS REPORT
status date opened date closed
open 2/4/2008
closed 2/4/2008 2/18/2008
closed 2/13/2008 3/15/2008
open 2/24/2008
pending 2/23/2008
pending 3/1/2008


ACTIVITY REPORT
activity week total new total closed total
items this wk open this wk
closed
2/3/2008 2/9/2008 2 2 2 0 0
2/10/2008 2/16/2008 3 1 3 0 0
2/17/2008 2/23/2008 3 0 2 1 1
2/24/2008 3/1/2008
3/2/2008 3/8/2008

Selecting date range for counts
 

It worked! Thank you so much!

"Barb Reinhardt" wrote:

ForOpen this week try this:

=SUMPRODUCT(--($B$3:$B$8=A17),--($B$3:$b$8=B17))

For Closed This week try

=SUMPRODUCT(--($C$3:$C$8=A17),--($C$3:$C$8=B17))

--
HTH,
Barb Reinhardt



"bokey" wrote:

Thanks for your response. I did figure out how to count the total number of
items by selecting a range and counting non-blank cells. The other 4 items
are more difficult. I'm trying to use the countif function but I haven't
been able to figure it out yet.

"Barb Reinhardt" wrote:

I'm thinking my reply was for Open AND closed this week.
--
HTH,
Barb Reinhardt



"Barb Reinhardt" wrote:

Here's a start.

For Total Items try this:


=SUMPRODUCT(--($B$3:$B$8=A17),--(ISNUMBER($C$3:$C$8)),--($C$3:$C$8=B17))

B3:B8 is the start date
C3:C8 is the end date
A17 is the beginning of the date range to test
B17 is the end of the date range to test.

I don't have time to figure out the rest. This should get you started.
HTH,
Barb Reinhardt



"bokey" wrote:

I have two worksheets (example below). The first is a status report that
lists status, date reported, date closed. The second worksheet I have to
create is an Activity report. Each week, the activity report must show
1) a count of the total number of items in the status worksheet as of that
week
2) a count of how many new items were opened during the week
3) a count of how many items are in open status as of that week
4) a count of how many items were closed that week
5) a count of how many items are in closed status as of that week

What formulas can I use in the activity report to give me the counts of
these 5 items each week?

Thanking anyone who can help me:)

STATUS REPORT
status date opened date closed
open 2/4/2008
closed 2/4/2008 2/18/2008
closed 2/13/2008 3/15/2008
open 2/24/2008
pending 2/23/2008
pending 3/1/2008


ACTIVITY REPORT
activity week total new total closed total
items this wk open this wk
closed
2/3/2008 2/9/2008 2 2 2 0 0
2/10/2008 2/16/2008 3 1 3 0 0
2/17/2008 2/23/2008 3 0 2 1 1
2/24/2008 3/1/2008
3/2/2008 3/8/2008