count within a range

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

Hi,
I've got a column with a list of times and want to
calculate the number of instances in the list that fall
within 12 hours.
ie. In the table below C6 should show the number of cells
in column B that are within +/-12 hours of B6

A B C
1 Delivery Time No. Deliveries within 12 Hours
2 6/01/2004 0:01
3 7/01/2004 0:01
4 13/01/2004 0:01
5 28/01/2004 0:01
6 1/02/2004 1:43
7 1/02/2004 3:05
8 1/02/2004 5:54
9 1/02/2004 8:50
10 3/02/2004 3:07
11 3/02/2004 18:48

Many thanks in advance for your help.
 
=COUNTIF(B:B,">"&B6-0.5)-COUNTIF(B:B,">"&B6+0.5)+1

Note: the +1 at the end, includes the key cell (B6 in this case) in the
count......

0.5 = 12 hours to Excel who sees the period as "half a day"......


Vaya con Dios,
Chuck, CABGx3
 
Back
Top