Count function
 

Attempting to obtain a count on the number of records with values populated
in a particular field that are between a date range. Have tried the
different count functions and cannot seem to get this to work. Any ideas on
how best to approach this?
John

Count function
 

Count how may are above the first cut-off date, subtract how many are over
the second cut-off date
=COUNTIF(rng,""&DATE(yyyy1,mm1,dd1)) - COUNTIF(rng,"<"&DATE(yyyy2,mm2,dd2))

If using Excel 2007, you could use COUNTIFS
=COUNTIF(rng,""&DATE(yyyy1,mm1,dd1)),"<"&DATE(yyy y2,mm2,dd2))

In any version use SUMPRODUCT

=SUMPRODUCT(--(A1:A200DATE(yyyy1,mm1,dd1)), --(A1:A20<DATE(yyyy2,mm2,dd2)))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"John Peterson" <John wrote in message
...
Attempting to obtain a count on the number of records with values
populated
in a particular field that are between a date range. Have tried the
different count functions and cannot seem to get this to work. Any ideas
on
how best to approach this?
John

Count function
 

One way:

pre-XL07:

=SUMPRODUCT(--(A1:A100=DATE(2008,1,1)),
--(A1:A100<=DATE(2008,12,31)), --ISNUMBER(B1:B100))


XL07,ff:

=COUNTIFS(A:A,"=1/1/2008",A:A,"<=12/31/2008")


In article ,
John Peterson <John wrote:

Attempting to obtain a count on the number of records with values populated
in a particular field that are between a date range. Have tried the
different count functions and cannot seem to get this to work. Any ideas on
how best to approach this?
John