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


"--Viewpoint" wrote:

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

=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

"--Viewpoint" <(E-Mail Removed)> wrote in message
news:AE681FBF-C8D1-4B54-94F7-(E-Mail Removed)...
>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?

On Sun, 30 Dec 2007 05:30:00 -0800, Gary''s Student
<(E-Mail Removed)> wrote:

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


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.

--Viewpoint wrote:
>
> 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?

--

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

"--Viewpoint" <(E-Mail Removed)> a écrit dans le message
de news: AE681FBF-C8D1-4B54-94F7-(E-Mail Removed)...
>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?

dsum

"--Viewpoint" <(E-Mail Removed)> ¼¶¼g©ó¶l¥ó·s»D:AE681FBF-C8D1-4B54-94F7-(E-Mail Removed)...
>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?

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.

"ªá¥Ò¯Î" wrote:

> dsum
>
> "--Viewpoint" <(E-Mail Removed)> ¼¶¼g©ó¶l¥ó·s»D:AE681FBF-C8D1-4B54-94F7-(E-Mail Removed)...
> >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?
>
>
>