Excel - Count number of cells containing a specific date range

Asked By Viewpoin on 30-Dec-07 07:59 AM
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?

GarysStuden replied on 30-Dec-07 08:30 AM

Gary''s Student - gsnu200762
Bernard Liengme replied on 30-Dec-07 08:33 AM
For more details on SUMPRODUCT
Bob Phillips
J.E McGimpsey

best wishes & Happy New Year
Bernard V Liengme
Microsoft Excel MVP
remove caps from email
Ron Rosenfeld replied on 30-Dec-07 08:50 AM
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:




depending on which definition.
Dave Peterson replied on 30-Dec-07 10:13 AM
Another one:

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:

And J.E. McGimpsey has some notes at:

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

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


Dave Peterson
François replied on 30-Dec-07 04:45 PM

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

ªá¥Ò¯Î replied on 01-Jan-08 05:44 AM
JohnR replied to ªá¥Ò¯Î on 30-Dec-09 12:02 PM
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