count within a range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andrew - 23 Jun 2004 11:45 GMT

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.

Reply to this Message

CLR - 23 Jun 2004 13:42 GMT

=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

> Hi,
> I've got a column with a list of times and want to
[quoted text clipped - 17 lines]
>
> Many thanks in advance for your help.

Reply to this Message