Excel - Count number of cells containing a specific date range

I have a spreadsheet with dates in one column and data extends to columns to
the right of the date.  I need to do is count the number of cells that
contains data within a date range (for each month within one year).  Example:

For the month of January 2007, count the number of cells that contains dates
between 01/01/07-01/31/07.

Any suggestions for how to create the correct formula?

=COUNTIF(A1:A100,">="&DATE(2007,1,1))-COUNTIF(A1:A100,">="&DATE(2007,1,31))

--
Gary''s Student - gsnu200762

=SUMPRODUCT(--(MONTH(A1:A30)=1),--(YEAR(A1:A30)=2007))
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes & Happy New Year
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

On Sun, 30 Dec 2007 05:30:00 -0800, Gary''s Student



Your formula is inconsistent.

It includes 1/1/07 but not 1/31/07.

I would think by "between" the OP probably meant to include both dates, as is
the "common" usage.  Possibly he meant to exclude both dates, but I doubt that.

So either:

=COUNTIF(A1:A100,">="&DATE(2007,1,1))-COUNTIF(A1:A100,">"&DATE(2007,1,31))

or

=COUNTIF(A1:A100,">"&DATE(2007,1,1))-COUNTIF(A1:A100,">="&DATE(2007,1,31))

depending on which definition.
--ron

Another one:
=sumproduct(--(text(a2:a100,"yyyymm")="200701")

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers.  The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

==========
But you may want to drop the formula approach and use data|pivottable (xl2003
menu).

You can group by month and year and get the summary report pretty quickly.


--

Dave Peterson

Hello,

I would rather more advise you to use two conditions, such as :
Cell(... or [A...])> to the first day of the selected month,
and the "Datedif function" -> Datedif(old date, new date, "m") <1 for a
single month choice ...

François

dsum

what about:

=COUNTIFS(F57:F65,">=" & INDIRECT("B57"), F57:F65,"<=" & INDIRECT("B58"))

where F57:F65 is the date range,  B57 is the start date and B58 is the end
date.