Count on dates

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.
 
T

Teethless mama

=SUMPRODUCT(--(MONTH(B2:B6000)>3),--(MONTH(B2:B6000)<7),--(YEAR(B2:B6000)=2009))
 
V

Vijay DSK

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....
 
M

Mike H

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
 
V

Vijay DSK

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
 
R

Rick Rothstein

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.
 
R

Rick Rothstein

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)?
 
V

Vijay DSK

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
 
D

David Biddulph

When Rick said that you could check by using Format/Cells, what I think he
meant is that if you use Format/ Cells to change the date format
temporarily, for example to dd mmm yyyy, if the display in the cell changes
they are real dates, but if they don't change they are text. Try that.

Also, remember to check all the rows in the column. If, for example, you
have a field description saying "date" in cell B1, this would give a #VALUE!
error from the formula.
You could check with =SUM(--(ISTEXT(B1:B70000))) as an *array formula*
entered with Control Shift Enter. It should give zero if you have no text
entries in your range.
 
V

Vijay DSK

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.
 
R

Rick Rothstein

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
How do I combine 2 data sets using date? 2
separating date and time 5
How do I count the number of blank cells 0
SUMPRODUCT Function Compare Date 3
Count based on date criteria 1
Count if between date parameters 4
Counting cells with specific month in 7

Top