| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count on dates
Hi all,
Thanks for the previous help. Now i am struck with another problem hope you guys can help me out. My issue is i have two columns like Col1 Col2 Date date from to 15/04/2008 14/03/2009 ..... and so on (Sixty thousand records with different dates) now i want to filter the data on Month wise for instance... i want to count all the values under the month Apr-09, May-09, June-09 etc. Hope i am clear in asking the question. A speedy help will be greatly appreciated. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count on dates
=SUMPRODUCT(--(MONTH(B2:B6000)3),--(MONTH(B2:B6000)<7),--(YEAR(B2:B6000)=2009))
"Vijay DSK" wrote: Hi all, Thanks for the previous help. Now i am struck with another problem hope you guys can help me out. My issue is i have two columns like Col1 Col2 Date date from to 15/04/2008 14/03/2009 .... and so on (Sixty thousand records with different dates) now i want to filter the data on Month wise for instance... i want to count all the values under the month Apr-09, May-09, June-09 etc. Hope i am clear in asking the question. A speedy help will be greatly appreciated. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count on dates
Dear friend the result of your advice is as follows
15/04/2008 14/03/2009 0 16/04/2008 15/03/2009 0 17/04/2008 16/03/2009 0 18/04/2008 17/03/2009 0 19/04/2008 18/03/2009 0 20/04/2008 19/03/2009 0 21/04/2008 20/03/2009 0 22/04/2008 21/03/2009 0 23/04/2008 22/03/2009 0 24/04/2008 23/03/2009 0 25/04/2008 24/03/2009 0 26/04/2008 25/03/2009 0 27/04/2008 26/03/2009 0 28/04/2008 27/03/2009 0 29/04/2008 28/03/2009 0 30/04/2008 29/03/2009 0 Please advice.... "Teethless mama" wrote: =SUMPRODUCT(--(MONTH(B2:B6000)3),--(MONTH(B2:B6000)<7),--(YEAR(B2:B6000)=2009)) "Vijay DSK" wrote: Hi all, Thanks for the previous help. Now i am struck with another problem hope you guys can help me out. My issue is i have two columns like Col1 Col2 Date date from to 15/04/2008 14/03/2009 .... and so on (Sixty thousand records with different dates) now i want to filter the data on Month wise for instance... i want to count all the values under the month Apr-09, May-09, June-09 etc. Hope i am clear in asking the question. A speedy help will be greatly appreciated. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count on dates
Hi,
Like Ashish has already pointed out there isn't enough information to answer this:- 15/04/2008 14/03/2009 0 16/04/2008 15/03/2009 0 17/04/2008 16/03/2009 0 18/04/2008 17/03/2009 0 19/04/2008 18/03/2009 0 i want to count all the values under the month Apr-09, May-09, June-09 etc. From the data posted above which dates are in April. Each pair of dates covers and entire year!! What values are we counting? Mike "Vijay DSK" wrote: Dear friend the result of your advice is as follows 15/04/2008 14/03/2009 0 16/04/2008 15/03/2009 0 17/04/2008 16/03/2009 0 18/04/2008 17/03/2009 0 19/04/2008 18/03/2009 0 20/04/2008 19/03/2009 0 21/04/2008 20/03/2009 0 22/04/2008 21/03/2009 0 23/04/2008 22/03/2009 0 24/04/2008 23/03/2009 0 25/04/2008 24/03/2009 0 26/04/2008 25/03/2009 0 27/04/2008 26/03/2009 0 28/04/2008 27/03/2009 0 29/04/2008 28/03/2009 0 30/04/2008 29/03/2009 0 Please advice.... "Teethless mama" wrote: =SUMPRODUCT(--(MONTH(B2:B6000)3),--(MONTH(B2:B6000)<7),--(YEAR(B2:B6000)=2009)) "Vijay DSK" wrote: Hi all, Thanks for the previous help. Now i am struck with another problem hope you guys can help me out. My issue is i have two columns like Col1 Col2 Date date from to 15/04/2008 14/03/2009 .... and so on (Sixty thousand records with different dates) now i want to filter the data on Month wise for instance... i want to count all the values under the month Apr-09, May-09, June-09 etc. Hope i am clear in asking the question. A speedy help will be greatly appreciated. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count on dates
Hi,
On what column would you like to check for Apr-09, Jun-09 etc. Please clarify. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Vijay DSK" wrote in message ... Hi all, Thanks for the previous help. Now i am struck with another problem hope you guys can help me out. My issue is i have two columns like Col1 Col2 Date date from to 15/04/2008 14/03/2009 .... and so on (Sixty thousand records with different dates) now i want to filter the data on Month wise for instance... i want to count all the values under the month Apr-09, May-09, June-09 etc. Hope i am clear in asking the question. A speedy help will be greatly appreciated. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count on dates
Dear ashish i want to check up on "To" column ( ie., column 2) "Ashish Mathur" wrote: Hi, On what column would you like to check for Apr-09, Jun-09 etc. Please clarify. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Vijay DSK" wrote in message ... Hi all, Thanks for the previous help. Now i am struck with another problem hope you guys can help me out. My issue is i have two columns like Col1 Col2 Date date from to 15/04/2008 14/03/2009 .... and so on (Sixty thousand records with different dates) now i want to filter the data on Month wise for instance... i want to count all the values under the month Apr-09, May-09, June-09 etc. Hope i am clear in asking the question. A speedy help will be greatly appreciated. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count on dates
Dear friends,
thanks for your speedy help and Mike my apologies if i didnot ask the question properly. I want to find the number of cells in column2 basing on the month April. example i found 16 cells in the example which i pasted in this post. Like wise on every month. hope i am clear in asking this time. Thanks once again "Vijay DSK" wrote: Dear ashish i want to check up on "To" column ( ie., column 2) "Ashish Mathur" wrote: Hi, On what column would you like to check for Apr-09, Jun-09 etc. Please clarify. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Vijay DSK" wrote in message ... Hi all, Thanks for the previous help. Now i am struck with another problem hope you guys can help me out. My issue is i have two columns like Col1 Col2 Date date from to 15/04/2008 14/03/2009 .... and so on (Sixty thousand records with different dates) now i want to filter the data on Month wise for instance... i want to count all the values under the month Apr-09, May-09, June-09 etc. Hope i am clear in asking the question. A speedy help will be greatly appreciated. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count on dates
Maybe this
=SUMPRODUCT(--(MONTH(A1:A16)=4)) Mike "Vijay DSK" wrote: Dear friends, thanks for your speedy help and Mike my apologies if i didnot ask the question properly. I want to find the number of cells in column2 basing on the month April. example i found 16 cells in the example which i pasted in this post. Like wise on every month. hope i am clear in asking this time. Thanks once again "Vijay DSK" wrote: Dear ashish i want to check up on "To" column ( ie., column 2) "Ashish Mathur" wrote: Hi, On what column would you like to check for Apr-09, Jun-09 etc. Please clarify. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Vijay DSK" wrote in message ... Hi all, Thanks for the previous help. Now i am struck with another problem hope you guys can help me out. My issue is i have two columns like Col1 Col2 Date date from to 15/04/2008 14/03/2009 .... and so on (Sixty thousand records with different dates) now i want to filter the data on Month wise for instance... i want to count all the values under the month Apr-09, May-09, June-09 etc. Hope i am clear in asking the question. A speedy help will be greatly appreciated. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count on dates
Give this a try for April 2009...
=SUMPRODUCT((MONTH(B1:B70000)=4)*(YEAR(B1:B70000)= 2009)) Change the 4 (month = April) and 2009 (year of interest) to suit your conditions. I used 70000 rows to make sure I covered the 60000 rows (records) you mentioned in your first posting... the number used (same in both ranges) has to be larger than the number of actual records being examined. -- Rick (MVP - Excel) "Vijay DSK" wrote in message ... Dear friends, thanks for your speedy help and Mike my apologies if i didnot ask the question properly. I want to find the number of cells in column2 basing on the month April. example i found 16 cells in the example which i pasted in this post. Like wise on every month. hope i am clear in asking this time. Thanks once again "Vijay DSK" wrote: Dear ashish i want to check up on "To" column ( ie., column 2) "Ashish Mathur" wrote: Hi, On what column would you like to check for Apr-09, Jun-09 etc. Please clarify. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Vijay DSK" wrote in message ... Hi all, Thanks for the previous help. Now i am struck with another problem hope you guys can help me out. My issue is i have two columns like Col1 Col2 Date date from to 15/04/2008 14/03/2009 .... and so on (Sixty thousand records with different dates) now i want to filter the data on Month wise for instance... i want to count all the values under the month Apr-09, May-09, June-09 etc. Hope i am clear in asking the question. A speedy help will be greatly appreciated. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count on dates
Rick,
When i tried that i am getting a "#value" error Help me out "Rick Rothstein" wrote: Give this a try for April 2009... =SUMPRODUCT((MONTH(B1:B70000)=4)*(YEAR(B1:B70000)= 2009)) Change the 4 (month = April) and 2009 (year of interest) to suit your conditions. I used 70000 rows to make sure I covered the 60000 rows (records) you mentioned in your first posting... the number used (same in both ranges) has to be larger than the number of actual records being examined. -- Rick (MVP - Excel) "Vijay DSK" wrote in message ... Dear friends, thanks for your speedy help and Mike my apologies if i didnot ask the question properly. I want to find the number of cells in column2 basing on the month April. example i found 16 cells in the example which i pasted in this post. Like wise on every month. hope i am clear in asking this time. Thanks once again "Vijay DSK" wrote: Dear ashish i want to check up on "To" column ( ie., column 2) "Ashish Mathur" wrote: Hi, On what column would you like to check for Apr-09, Jun-09 etc. Please clarify. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Vijay DSK" wrote in message ... Hi all, Thanks for the previous help. Now i am struck with another problem hope you guys can help me out. My issue is i have two columns like Col1 Col2 Date date from to 15/04/2008 14/03/2009 .... and so on (Sixty thousand records with different dates) now i want to filter the data on Month wise for instance... i want to count all the values under the month Apr-09, May-09, June-09 etc. Hope i am clear in asking the question. A speedy help will be greatly appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I want to count the total Number of dates between two dates How? | Excel Worksheet Functions | |||
| Count Dates? | Excel Worksheet Functions | |||
| Count dates | Excel Worksheet Functions | |||
| COUNT IF BETWEEN DATES | Excel Worksheet Functions | |||
| need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
![Vijay DSK[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
