Count number of cells containing a specific date range

  • Thread starter Thread starter --Viewpoint
  • Start date Start date
V

--Viewpoint

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))
Click to expand...


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