Count on dates

  • Thread starter Thread starter Vijay DSK
  • Start date Start date
V

Vijay DSK

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.
 
=SUMPRODUCT(--(MONTH(B2:B6000)>3),--(MONTH(B2:B6000)<7),--(YEAR(B2:B6000)=2009))
 
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....
 
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

From the data posted above which dates are in April. Each pair of dates
covers and entire year!! What values are we counting?

Mike
 
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
 
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.
 
I tested the formula before I posted it and it worked for me at that time.
Do you have *real* dates in Column B or are they text values (you can check
by looking at Format/Cells)?
 
Rick
My dates are *real* dates and for your information my date settings are in
dd/mm/yyyy.
Please look into this and advice.
Thanks once again
 
Friends,
Thanks once again for the help. Rick, once again thanks, the formula advised
by you a master piece. Only mistake i did was there are some gaps in the
column which were filled by "-", so the formula doesn't worked out.

Any how thanks once again you people.
 
So you don't have an answer to your question yet then, right?

Give this array-entered** formula a try...

=SUM(IF(ISNUMBER(B1:B65535),MONTH(B1:B65535)=11,0)*IF(ISNUMBER(B1:B65535),YEAR(B1:B65535)=2008,0))

**Commit the formula with Ctrl+Shift+Enter, not just with Enter by itself.
 

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

Similar Threads

time_report_date-wise_employee-wise 6
SUMPRODUCT Function Compare Date 3
Count based on date criteria 1
Access Count dates within a Month 4
Count based on multiple criteria 2
separating date and time 5
Counting cells with specific month in 7
XIRR Function - large IRRs 1

Back
Top