• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Culling similar dates from a series

J

jdmaybee

New Member
If I have a column of dates is there any way I can find out how many of them were for a particular period/month without setting up another column that has the formula =MONTH(x1)to interpret the data? In other words "How many January 2009's are there in this list?"
 
=sumproduct((range>=date(yr,month, day)*(range<=date(yr,month, day))
 
Whoops

=sumproduct((range>=date(yr,month, day))*(range<=date(yr,month, day)))
 
I have tried both of the following:

=SUMPRODUCT((A7:A180>=DATE(yr,month, day))*(A7:A180<=DATE(yr,month, day)))

=SUMPRODUCT(--(A7:A180>=DATE(yr,month, day))*(A7:A180<=DATE(yr,month, day)))

The dates are Excel dates in this format:

01/05/11

to

03/05/11
 
It needs to be

=SUMPRODUCT((A7:A180>=DATE(2011,05, 01))*(A7:A180<=DATE(2011, 05, 03)))

or

=SUMPRODUCT((A7:A180>=DATE(Year(date from cell),Month(date from cell),Day(date from cell)))*(A7:A180<=DATE(Year(date to cell),Month(date to cell),Day(date to cell))))

Where Date From Cell and Date to Cell are cell references eg: D1 etc
 
Hui, This function will not work when years are different. for example, I just want to get that who all are whose birth day falls in march. Obviously years may differ. Please suggest if there is any method to extract this information without taking the help of another column having formula =month(X1).
 
To find how many cells are in March

=SUMPRODUCT(1*(MONTH(A1:A20)=3))


To find how many cells are in March and 2009

=SUMPRODUCT((MONTH(A1:A20)=3)*(Year(A1:A20)=2009))


Change ranges to suit
 
DJ,

Rather than using MONTH and YEAR, try using the TEXT function like this:

=SUMPRODUCT(--(TEXT(A7:A180,"mmm")=TEXT(X1,"mmm")))


Where X1 contains the date you want. (the format "mmm" displays the 3 letter abbreviation for month). Alternatively, you could write:

=SUMPRODUCT(--(TEXT(A7:A180,"mmm")="Mar"))


The Text function is nice in that it won't throw an error if a text string is encountered, as the MONTH/YEAR functions will.
 
I tried Hui's suggestion and for some reason got a Value error. I am mystified why because the logic looks flawless. If you have any idea why I would be heavily indebted to you. I am using Excel 2002 if that has anything to do with it.


Luke's response =SUMPRODUCT(--(TEXT(A7:A180,"mmm")="Mar"))

is exactly what I am after.


Thanks to you both!
 
I don't get this I tried Hui's solution =SUMPRODUCT(1*(MONTH(A1:A20)=3))

again just a few minutes ago and it worked like a dream! What do you suppose is going on here??
 
You must log in or register to reply here.
Back
Top