Count function

  • Thread starter Thread starter John Peterson
  • Start date Start date
J

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