count if column A is 1-2 and column B is 03/02/08 to 03/09/08
 

I have two worksheets (Summary and Detail) in my workbook.

On the summary tab I have two columns (Priority 1-2) and (Priority 3-5).
For each column I need to count on the detail tab, the number of times the
numbers 1 or 2 appear in Prioirty column when the dates in column B are
03/02/08 and 03/09/08, and then I need the count for when the Priority is 3-5
for the same dates.

Priority Date
1 02/28/08
1 03/02/08
5 03/08/08
2 03/08/08
3 03/08/08
2 03/09/08
2 03/15/08
1 03/15/08

count if column A is 1-2 and column B is 03/02/08 to 03/09/08
 

I hit post too quickly. There is a third variable.

In addition to Priority and Date in the Detail tab, there is also a status
column. So I need to restate the problem.

For each column I need to count on the detail tab, the number of times the
numbers 1 or 2 appear in Prioirty column when the dates in column B are
03/02/08 and 03/09/08 AND THE STATUS COLUMN is equal to Open, and then I need the count for when the Priority is 3-5 for the same dates.

Priority Date Status
1 02/28/08 Closed
1 03/02/08 Open
5 03/08/08 Pending
2 03/08/08 Open
3 03/08/08 Open
2 03/09/08 Open
2 03/15/08 Open
1 03/15/08 Closed

"bokey" wrote:

I have two worksheets (Summary and Detail) in my workbook.

On the summary tab I have two columns (Priority 1-2) and (Priority 3-5).
For each column I need to count on the detail tab, the number of times the
numbers 1 or 2 appear in Prioirty column when the dates in column B are
03/02/08 and 03/09/08, and then I need the count for when the Priority is 3-5
for the same dates.

Priority Date
1 02/28/08
1 03/02/08
5 03/08/08
2 03/08/08
3 03/08/08
2 03/09/08
2 03/15/08
1 03/15/08

count if column A is 1-2 and column B is 03/02/08 to 03/09/08
 

For 1-2 assuming you don't have anything less

=SUMPRODUCT(--(A2:A13<=2),--(B2:B13=DATE(2008,3,2)),--(B2:B13<=DATE(2008,3,9)),--(C2:C13="Open"))


for 3-5


=SUMPRODUCT(--(A2:A13=3),--(A2:A13<=5),--(B2:B13=DATE(2008,3,2)),--(B2:B13<=DATE(2008,3,9)),--(C2:C13="Open"))


Adapt to fit accordingly with your own cell ranges


--


Regards,


Peo Sjoblom


"bokey" wrote in message
...
I hit post too quickly. There is a third variable.

In addition to Priority and Date in the Detail tab, there is also a status
column. So I need to restate the problem.

For each column I need to count on the detail tab, the number of times the
numbers 1 or 2 appear in Prioirty column when the dates in column B are
03/02/08 and 03/09/08 AND THE STATUS COLUMN is equal to Open, and then I
need the count for when the Priority is 3-5 for the same dates.

Priority Date Status
1 02/28/08 Closed
1 03/02/08 Open
5 03/08/08 Pending
2 03/08/08 Open
3 03/08/08 Open
2 03/09/08 Open
2 03/15/08 Open
1 03/15/08 Closed

"bokey" wrote:

I have two worksheets (Summary and Detail) in my workbook.

On the summary tab I have two columns (Priority 1-2) and (Priority 3-5).
For each column I need to count on the detail tab, the number of times
the
numbers 1 or 2 appear in Prioirty column when the dates in column B are
03/02/08 and 03/09/08, and then I need the count for when the Priority is
3-5
for the same dates.

Priority Date
1 02/28/08
1 03/02/08
5 03/08/08
2 03/08/08
3 03/08/08
2 03/09/08
2 03/15/08
1 03/15/08

count if column A is 1-2 and column B is 03/02/08 to 03/09/08
 

Thank you so much! The Status and Dates are working great but for some
reason the priority is not. My formula is below. The detail worksheet is
called ADV CLM and this formula is in the Summary worksheet. Fields A37 and
B37 are on the Summary worksheet and represent begin/end of the week. A37 is
03/02/08 and B37 is 03/09/08.

I have 5 Open items for that date range, two of them are status 1-2, and
three of them are status 3-5. I am getting a count of 1 for each.

=SUMPRODUCT(--('ADV CLM'!I2:I500<=2),--('ADV CLM'!K2:K500=A37))--('ADV
CLM'!K2:K500<=B37)--('ADV CLM'!E2:E500="Open")


"bokey" wrote:

I have two worksheets (Summary and Detail) in my workbook.

On the summary tab I have two columns (Priority 1-2) and (Priority 3-5).
For each column I need to count on the detail tab, the number of times the
numbers 1 or 2 appear in Prioirty column when the dates in column B are
03/02/08 and 03/09/08, and then I need the count for when the Priority is 3-5
for the same dates.

Priority Date
1 02/28/08
1 03/02/08
5 03/08/08
2 03/08/08
3 03/08/08
2 03/09/08
2 03/15/08
1 03/15/08

count if column A is 1-2 and column B is 03/02/08 to 03/09/08
 

You forgot 2 commas and a parenthesis at the end

=SUMPRODUCT(--('ADV CLM'!I2:I500<=2),--('ADV CLM'!K2:K500=A37)),--('ADV
CLM'!K2:K500<=B37),--('ADV CLM'!E2:E500="Open"))

If those were typos and the formula works but gives the wrong result then
you might have extra spaces in the
priority column


--


Regards,


Peo Sjoblom



"bokey" wrote in message
...
Thank you so much! The Status and Dates are working great but for some
reason the priority is not. My formula is below. The detail worksheet is
called ADV CLM and this formula is in the Summary worksheet. Fields A37
and
B37 are on the Summary worksheet and represent begin/end of the week. A37
is
03/02/08 and B37 is 03/09/08.

I have 5 Open items for that date range, two of them are status 1-2, and
three of them are status 3-5. I am getting a count of 1 for each.

=SUMPRODUCT(--('ADV CLM'!I2:I500<=2),--('ADV CLM'!K2:K500=A37))--('ADV
CLM'!K2:K500<=B37)--('ADV CLM'!E2:E500="Open")


"bokey" wrote:

I have two worksheets (Summary and Detail) in my workbook.

On the summary tab I have two columns (Priority 1-2) and (Priority 3-5).
For each column I need to count on the detail tab, the number of times
the
numbers 1 or 2 appear in Prioirty column when the dates in column B are
03/02/08 and 03/09/08, and then I need the count for when the Priority is
3-5
for the same dates.

Priority Date
1 02/28/08
1 03/02/08
5 03/08/08
2 03/08/08
3 03/08/08
2 03/09/08
2 03/15/08
1 03/15/08