How to count the occurrence?

E

Eric

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
 
S

Shane Devenshire

Hi,

Try this

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

Adjust the range to match your data.
 
E

Eric

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
 
D

Dave Peterson

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top