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

[bokey]

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

 

[bokey]

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.

 

[Peo Sjoblom]

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]

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

 

[Peo Sjoblom]

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