Count Cells that Contain Specific Date Range

[TheKB]

I have a spreadsheet with dates ranging throughout the year. I want to count all the cells that contain January dates, for example. How can I accompllish this?

Thanks in advance!

 

[Robert Mika]

If your data are in column A
Enter 01/01/2010 in B1 then 31/01/2010 in C1 an then in D1
=SUMPRODUCT(--(A1:A16>B1),--(A1:A16<C1)) (example)

 

[schielrn]

What version of Excel are you using? If you are using 2007 or newer you can filter for January and then it will tell you how many cells are filtered, otherwise you can probably use a formula like this in any case:

=sumproduct(--(month(A1:A1000)=1))

Hope that helps.

 

[TheKB]

Thank you - haven't tried the 1st suggestion yet (doing that next) yet the 2nd seemed promising until it returned all blank rows as January. I'm using Excel 2003.

Appreciate both of you!

 

[TheKB]

OK - USER ERROR!

I got it - thank you!!

 

[danzon]

use a helper column B

=MONTH(A1)
and drag down


then in C1

=COUNTIF(B:B,1)

 

[Jonmo1]

Blanks are considered as 0.
Believe it or not, Excel considers the number 0 to be the date
January 0 1900
I know it doesn't make sense, but it is what it is.
Basically because the number 1 is January 1st 1900.
Excel doesn't understand that dates existed in the 1800's..
So it makes 0 Jan 0 1900..


Anyway, that's the why...

You may be better off accounting for the year anyway..
Because dates in january of 2009 will be counted the same as january in 2010

Try

=SUMPRODUCT(--(TEXT(A1:A1000,"mmmyyyy")="Jan2010"))

Hope that helps.

 

[TheKB]

thanks everyone