Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count number of cells and total in one column, based on another column suffix | Excel Worksheet Functions | |||
Count entries in one column based on values in another column | Excel Worksheet Functions | |||
how to count a column based on condition of another column | Excel Worksheet Functions | |||
SUM/COUNT column(s) based on specific value present within the column | Excel Worksheet Functions | |||
Can I count values in column 1 if criteria in column 2 are met | Excel Worksheet Functions |