How to count the occurrence?
 

Does anyone have any suggestions on how to count the occurrence?
There is a list of date under m column, I would like to determine the
occurrence fall into following category without concerning the year.
For example, 17/04/06 is equal or large than 10/4/09 and less than 25/4/09,
therefore 1 will be asssigned into 10/04/09 category.
Does anyone have any suggestions on how to do it in excel?
Thanks in advance for any suggestions
Eric

26/03/09
10/04/09
25/04/09
10/05/09
26/05/09
10/06/09
26/06/09
12/07/09
28/07/09
12/08/09
28/08/09
12/09/09
27/09/09
13/10/09
28/10/09
13/11/09
27/11/09
12/12/09
26/12/09
10/01/09
25/01/09
09/02/09
24/02/09
10/03/09

How to count the occurrence?
 

Hi,

Try this

=SUMPRODUCT(--(MONTH(A1:A12)=4),--(DAY(A1:A12)=10),--(DAY(A1:A12)<=25))

Adjust the range to match your data.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Eric" wrote:

Does anyone have any suggestions on how to count the occurrence?
There is a list of date under m column, I would like to determine the
occurrence fall into following category without concerning the year.
For example, 17/04/06 is equal or large than 10/4/09 and less than 25/4/09,
therefore 1 will be asssigned into 10/04/09 category.
Does anyone have any suggestions on how to do it in excel?
Thanks in advance for any suggestions
Eric

26/03/09
10/04/09
25/04/09
10/05/09
26/05/09
10/06/09
26/06/09
12/07/09
28/07/09
12/08/09
28/08/09
12/09/09
27/09/09
13/10/09
28/10/09
13/11/09
27/11/09
12/12/09
26/12/09
10/01/09
25/01/09
09/02/09
24/02/09
10/03/09

How to count the occurrence?
 

If the given date is 28/03/09, then it does not work, the condition cannot
separate month and day and check separately.
Do you have any suggestions?
Thanks in advance for any suggestions
Eric

"Shane Devenshire" wrote:

Hi,

Try this

=SUMPRODUCT(--(MONTH(A1:A12)=4),--(DAY(A1:A12)=10),--(DAY(A1:A12)<=25))

Adjust the range to match your data.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Eric" wrote:

Does anyone have any suggestions on how to count the occurrence?
There is a list of date under m column, I would like to determine the
occurrence fall into following category without concerning the year.
For example, 17/04/06 is equal or large than 10/4/09 and less than 25/4/09,
therefore 1 will be asssigned into 10/04/09 category.
Does anyone have any suggestions on how to do it in excel?
Thanks in advance for any suggestions
Eric

26/03/09
10/04/09
25/04/09
10/05/09
26/05/09
10/06/09
26/06/09
12/07/09
28/07/09
12/08/09
28/08/09
12/09/09
27/09/09
13/10/09
28/10/09
13/11/09
27/11/09
12/12/09
26/12/09
10/01/09
25/01/09
09/02/09
24/02/09
10/03/09

How to count the occurrence?
 

If Shane's formula doesn't work, then that means that at least some of your data
isn't really dates. And the values that are really dates may not be the dates
that you intend.

Try selecting the range and giving it an unambiguous date format, like:
mmmm dd, yyyy

If there are values that still look like 28/03/09, that means that it's not a
date.

Then check the values that did change format against the original source to see
if the dates are what you expected.

If I imported the dates from an external source, I'd want to go back and make
sure that the dates were as intended.



Eric wrote:

If the given date is 28/03/09, then it does not work, the condition cannot
separate month and day and check separately.
Do you have any suggestions?
Thanks in advance for any suggestions
Eric

"Shane Devenshire" wrote:

Hi,

Try this

=SUMPRODUCT(--(MONTH(A1:A12)=4),--(DAY(A1:A12)=10),--(DAY(A1:A12)<=25))

Adjust the range to match your data.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Eric" wrote:

Does anyone have any suggestions on how to count the occurrence?
There is a list of date under m column, I would like to determine the
occurrence fall into following category without concerning the year.
For example, 17/04/06 is equal or large than 10/4/09 and less than 25/4/09,
therefore 1 will be asssigned into 10/04/09 category.
Does anyone have any suggestions on how to do it in excel?
Thanks in advance for any suggestions
Eric

26/03/09
10/04/09
25/04/09
10/05/09
26/05/09
10/06/09
26/06/09
12/07/09
28/07/09
12/08/09
28/08/09
12/09/09
27/09/09
13/10/09
28/10/09
13/11/09
27/11/09
12/12/09
26/12/09
10/01/09
25/01/09
09/02/09
24/02/09
10/03/09


--

Dave Peterson