Home |
Search |
Today's Posts |
#1
|
|||
|
|||
count dates within range by year and month
Column D contains a large number of rows of dates (in random order).
Column F contains a text status ("open" or "closed"). I would like to create a series of four columns: 01/2005 10 8 2 02/2005 12 9 3 03/2005 11 7 4 wherein: the first column is the criterion for counting, the second column is the number of entries for the target month, the third column is the number of open cases, and the fourth column is the number of closed cases. |
#2
|
|||
|
|||
try something like this
=sumproduct((month(daterng)=month(a2))*(year(dater ng)=year(a2))*(statusrng=" open")) -- Don Guillett SalesAid Software "Isaiah25" wrote in message ... Column D contains a large number of rows of dates (in random order). Column F contains a text status ("open" or "closed"). I would like to create a series of four columns: 01/2005 10 8 2 02/2005 12 9 3 03/2005 11 7 4 wherein: the first column is the criterion for counting, the second column is the number of entries for the target month, the third column is the number of open cases, and the fourth column is the number of closed cases. |
#3
|
|||
|
|||
Assuming the columns are on another sheet in A1:D1, etc
A1: test date B1: =SUMPRODUCT(--(MONTH(Sheet1!D1:D100)=MONTH(A1)) C1: =SUMPRODUCT(--(MONTH(Sheet1!D1:D100)=MONTH(A1),--(Sheet1!F1:F100="open")) D1: =B1-C1 and copy down -- HTH Bob Phillips "Isaiah25" wrote in message ... Column D contains a large number of rows of dates (in random order). Column F contains a text status ("open" or "closed"). I would like to create a series of four columns: 01/2005 10 8 2 02/2005 12 9 3 03/2005 11 7 4 wherein: the first column is the criterion for counting, the second column is the number of entries for the target month, the third column is the number of open cases, and the fourth column is the number of closed cases. |
#4
|
|||
|
|||
Don's solution worked... Thank you. I also tried Bob's and ran into an
issue when I tried to incorporate YEAR into the same logic. I have learned from you both, and thank you both VERY MUCH for your quick responses. Thank you. "Isaiah25" wrote: Column D contains a large number of rows of dates (in random order). Column F contains a text status ("open" or "closed"). I would like to create a series of four columns: 01/2005 10 8 2 02/2005 12 9 3 03/2005 11 7 4 wherein: the first column is the criterion for counting, the second column is the number of entries for the target month, the third column is the number of open cases, and the fourth column is the number of closed cases. |
#5
|
|||
|
|||
It would just be
=SUMPRODUCT(--(MONTH(Sheet1!D1:D100)=MONTH(A1)),--(YEAR(Sheet1!D1:D100)=Year (A1))) etc. -- HTH Bob Phillips "Isaiah25" wrote in message ... Don's solution worked... Thank you. I also tried Bob's and ran into an issue when I tried to incorporate YEAR into the same logic. I have learned from you both, and thank you both VERY MUCH for your quick responses. Thank you. "Isaiah25" wrote: Column D contains a large number of rows of dates (in random order). Column F contains a text status ("open" or "closed"). I would like to create a series of four columns: 01/2005 10 8 2 02/2005 12 9 3 03/2005 11 7 4 wherein: the first column is the criterion for counting, the second column is the number of entries for the target month, the third column is the number of open cases, and the fourth column is the number of closed cases. |
#6
|
|||
|
|||
|
#8
|
|||
|
|||
Then you have to sum them count them divide
or modify this array formula to suit. Array formulas must be entered/edited with ctrl+shift+enter =AVERAGE(IF(D1:D5="don",C1:C5)) -- Don Guillett SalesAid Software "Isaiah25" wrote in message ... Is there a way also to do a selective average based on the same data? There is a beginning date column and an ending date column for each entry and they want an average duration for for all cases in any given month. "Don Guillett" wrote: we all aim to please -- Don Guillett SalesAid Software "Isaiah25" wrote in message ... Don's solution worked... Thank you. I also tried Bob's and ran into an issue when I tried to incorporate YEAR into the same logic. I have learned from you both, and thank you both VERY MUCH for your quick responses. Thank you. "Isaiah25" wrote: Column D contains a large number of rows of dates (in random order). Column F contains a text status ("open" or "closed"). I would like to create a series of four columns: 01/2005 10 8 2 02/2005 12 9 3 03/2005 11 7 4 wherein: the first column is the criterion for counting, the second column is the number of entries for the target month, the third column is the number of open cases, and the fourth column is the number of closed cases. |
#9
|
|||
|
|||
What I'm working with now is:
=AVERAGE(IF(MONTH('DC NAP NCT Matrix'!D$2:D$1000)=MONTH(A2)*(YEAR('DC NAP NCT Matrix'!D$2:D$1000)=YEAR(A2),'DC NAP NCT Matrix'!S$2:SD$1000,)) where column S is the duration per case. "Isaiah25" wrote: Is there a way also to do a selective average based on the same data? There is a beginning date column and an ending date column for each entry and they want an average duration for for all cases in any given month. "Don Guillett" wrote: we all aim to please -- Don Guillett SalesAid Software "Isaiah25" wrote in message ... Don's solution worked... Thank you. I also tried Bob's and ran into an issue when I tried to incorporate YEAR into the same logic. I have learned from you both, and thank you both VERY MUCH for your quick responses. Thank you. "Isaiah25" wrote: Column D contains a large number of rows of dates (in random order). Column F contains a text status ("open" or "closed"). I would like to create a series of four columns: 01/2005 10 8 2 02/2005 12 9 3 03/2005 11 7 4 wherein: the first column is the criterion for counting, the second column is the number of entries for the target month, the third column is the number of open cases, and the fourth column is the number of closed cases. |
#10
|
|||
|
|||
Bob Phillips wrote...
.... =SUMPRODUCT(--(MONTH(Sheet1!D1:D100)=MONTH(A1)), --(YEAR(Sheet1!D1:D100)=Year(A1))) .... Alternative, =SUMPRODUCT(--(TEXT(Sheet1!D1:D100,"YYYYMM")=TEXT(A1,"YYYYMM"))) |
#11
|
|||
|
|||
You could used this based on Harlan's idea
=AVERAGE(IF(TEXT(MONTH('DC NAP NCT Matrix'!D2:D1000,"YYYYMM")=TEXT(A1,"YYYYMM"),MONTH ('DC NAP NCT Matrix'!S2:S1000)) of course still an array formula -- HTH Bob Phillips "Isaiah25" wrote in message ... What I'm working with now is: =AVERAGE(IF(MONTH('DC NAP NCT Matrix'!D$2:D$1000)=MONTH(A2)*(YEAR('DC NAP NCT Matrix'!D$2:D$1000)=YEAR(A2),'DC NAP NCT Matrix'!S$2:SD$1000,)) where column S is the duration per case. "Isaiah25" wrote: Is there a way also to do a selective average based on the same data? There is a beginning date column and an ending date column for each entry and they want an average duration for for all cases in any given month. "Don Guillett" wrote: we all aim to please -- Don Guillett SalesAid Software "Isaiah25" wrote in message ... Don's solution worked... Thank you. I also tried Bob's and ran into an issue when I tried to incorporate YEAR into the same logic. I have learned from you both, and thank you both VERY MUCH for your quick responses. Thank you. "Isaiah25" wrote: Column D contains a large number of rows of dates (in random order). Column F contains a text status ("open" or "closed"). I would like to create a series of four columns: 01/2005 10 8 2 02/2005 12 9 3 03/2005 11 7 4 wherein: the first column is the criterion for counting, the second column is the number of entries for the target month, the third column is the number of open cases, and the fourth column is the number of closed cases. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get count of records for a particular month and year | Excel Discussion (Misc queries) | |||
HELP - need to returns the current number of past month this year and ... | Excel Worksheet Functions | |||
sort dates by month and day not year | Excel Worksheet Functions | |||
Month Year Date Format | Excel Worksheet Functions | |||
How sort dates just by day/month and NOT year if all 3 given in ce | Excel Worksheet Functions |