count within a range

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.

Andrew,

Try:

=SUM(IF(ABS(B2-$B$2:$B$16)0.5,1,0))-1

This needs to be an array formula. That means you must hit ctrl-shift-enter instead of just enter after you type in the formula. If you do that, you'll see that your formula is displayed with curly brackets like:

={SUM(IF(ABS(B2-$B$2:$B$16)0.5,1,0))-1}

Art

"Andrew" wrote:

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


"Andrew" wrote in message
...
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.