Count numbers of entries
 

I need to count a numbers of entries in a given month and then I have to
compare with another set of entries that has to be greater than a certain
month. I also has to count the numbers of empty cells. Can I use a nested IF
function ?

It looks like this :

Date in Date finished
01-11-2004 03-01-2005
30-11-2004

I now want to calculate how many entries entered in November but not
finished in November. I want this for every month of the year.

Susanne

Hi Susanne,

Assuming that your data are in range [A2:B10] try the following:

=SUMPRODUCT((MONTH(A2:A10)=11)*(YEAR(A2:A10)=2004) *((MONTH(B2:B10)<11)+(YEAR(B2:B10)<2004)0))

Regards,
KL

"Susanne" wrote in message
...
I need to count a numbers of entries in a given month and then I have to
compare with another set of entries that has to be greater than a certain
month. I also has to count the numbers of empty cells. Can I use a nested
IF
function ?

It looks like this :

Date in Date finished
01-11-2004 03-01-2005
30-11-2004

I now want to calculate how many entries entered in November but not
finished in November. I want this for every month of the year.

Susanne

Hi KL

It does not work. Here is a little more of what my worksheet looks like
A is the date when the project started and K is when it finished, if it is
blank the project is not finished yet.

A ........... K
1 20-01-2005 20-01-2005
2 21-01-2005 24-01-2005
3 21-01-2005
4 21-01-2005
5 21-01-2005 21-02-2005
6 27-01-2005 01-02-2005
7 31-01-2005 25-02-2005
8 02-02-2005 02-02-2005
9 04-02-2005
10 28-02-2005 01-03-2005

I want to know the numbers of projects started in January but not finished
in January, the numbers of projects started in January and finished in
January, the numbers of projects started in February but not finished in
February and last the numbers of projects started in February and finished in
February.

Susanne

"KL" wrote:

Hi Susanne,

Assuming that your data are in range [A2:B10] try the following:

=SUMPRODUCT((MONTH(A2:A10)=11)*(YEAR(A2:A10)=2004) *((MONTH(B2:B10)<11)+(YEAR(B2:B10)<2004)0))

Regards,
KL

"Susanne" wrote in message
...
I need to count a numbers of entries in a given month and then I have to
compare with another set of entries that has to be greater than a certain
month. I also has to count the numbers of empty cells. Can I use a nested
IF
function ?

It looks like this :

Date in Date finished
01-11-2004 03-01-2005
30-11-2004

I now want to calculate how many entries entered in November but not
finished in November. I want this for every month of the year.

Susanne

Hi Susanne,

It does not work.

What do you mean it does not work? What error do you get?

Here is a little more of what my worksheet looks like

One possible cause of the problem I see is that have totally chaged both the
data and the conditions since your last post:

The sample data provided previously showed:

1) Dates in 2004 Vs. 2005 in the new example
2) Date Start & Date Finish looked like contiguous columns Vs. [A] & [K] in
the new example
3) The condition was to find dates in November Vs. January in the new
example.

Obviously, the formula needs to be adjusted to these changes.

Based on the new sample, try the following formula to produce the count for
dates in January.
=SUMPRODUCT((MONTH($A$2:$A$10)=1)*(YEAR($A$2:$A$10 )=2005)*((MONTH($K$2:$K$10)<1)+(YEAR($K$2:$K$10)< 2005)0))

If you want to do the same for several months/years just substitute the
values with the reference to cells that contain the parameters:
=SUMPRODUCT((MONTH($A$2:$A$10)=A14)*(YEAR($A$2:$A$ 10)=B14)*((MONTH($K$2:$K$10)<A14)+(YEAR($K$2:$K$1 0)<B14)0))

where [A14] contains the number of month and [B14] the year.

Noow you can fill the cells from [A15] to [A25] with rest of month numbers,
[B15] to [B25] with the year and copy the formula down.


Regards,
KL

Hi KL

It works now. Thanks.
By the way my first data was just an ex. and I tried to use your tip
according to my own data but that did not come out right.

Susanne

"KL" wrote:

Hi Susanne,

It does not work.

What do you mean it does not work? What error do you get?

Here is a little more of what my worksheet looks like

One possible cause of the problem I see is that have totally chaged both the
data and the conditions since your last post:

The sample data provided previously showed:

1) Dates in 2004 Vs. 2005 in the new example
2) Date Start & Date Finish looked like contiguous columns Vs. [A] & [K] in
the new example
3) The condition was to find dates in November Vs. January in the new
example.

Obviously, the formula needs to be adjusted to these changes.

Based on the new sample, try the following formula to produce the count for
dates in January.
=SUMPRODUCT((MONTH($A$2:$A$10)=1)*(YEAR($A$2:$A$10 )=2005)*((MONTH($K$2:$K$10)<1)+(YEAR($K$2:$K$10)< 2005)0))

If you want to do the same for several months/years just substitute the
values with the reference to cells that contain the parameters:
=SUMPRODUCT((MONTH($A$2:$A$10)=A14)*(YEAR($A$2:$A$ 10)=B14)*((MONTH($K$2:$K$10)<A14)+(YEAR($K$2:$K$1 0)<B14)0))

where [A14] contains the number of month and [B14] the year.

Noow you can fill the cells from [A15] to [A25] with rest of month numbers,
[B15] to [B25] with the year and copy the formula down.


Regards,
KL