Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to count the number of occurrence within string? | Excel Discussion (Misc queries) | |||
How to count the occurrence? | Excel Discussion (Misc queries) | |||
How to count occurrence? | Excel Discussion (Misc queries) | |||
count occurrence between two specific dates | Excel Discussion (Misc queries) | |||
Count occurrence of character within a cell | Excel Discussion (Misc queries) |