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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.