Count function

[John Peterson]

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

 

[Bernard Liengme]

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(yyyy2,mm2,dd2))

In any version use SUMPRODUCT

=SUMPRODUCT(--(A1:A200>DATE(yyyy1,mm1,dd1)), --(A1:A20<DATE(yyyy2,mm2,dd2)))
best wishes

 

[JE McGimpsey]

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")