count number of unique records for each date

[nish1585]

Hi,

I have a large report, that I want to be able to count the number of vehicles that worked on a specific date.

This is how the data is being presented:-

A B C
1 Truck: Truck 1 SEMI TRAILER
2 Hauler: abc cartage
3 20/04/2010 12:06:13 PM 8158540
4 20/04/2010 12:14:22 PM 8158541
5 20/04/2010 12:28:36 PM 8158542
6 20/04/2010 1:04:34 PM 8158543
7 20/04/2010 1:09:23 PM 8158544
8 20/04/2010 1:27:27 PM 8158545
9 20/04/2010 1:36:14 PM 8158546
10 20/04/2010 1:41:15 PM 8158547
11 20/04/2010 1:41:58 PM 8158548
12 20/04/2010 2:05:58 PM 8158549
13 20/04/2010 2:08:48 PM 8158550
14 20/04/2010 2:29:29 PM 8158551
15 20/04/2010 2:35:46 PM 8158552
16 20/04/2010 2:41:58 PM 8158553
17 20/04/2010 3:57:38 PM 8158554
18 20/04/2010 4:00:41 PM 8158555
19 20/04/2010 4:05:00 PM 8158556
20 20/04/2010 4:24:43 PM 8158557
21 Truck: Truck 2 Tandum
22 Hauler: abc cartage
23 18/04/2010 6:59:56 PM 8158423
24 18/04/2010 9:42:33 PM 8158446
25 28/04/2010 7:17:20 PM 8158769
26 13/04/2010 8:45:50 PM 8158324
27 13/04/2010 11:28:15 PM 8158332
28 14/04/2010 1:49:27 AM 8158342
29 14/04/2010 2:57:48 AM 8158352
30 23/04/2010 7:04:18 AM 8158677
31 23/04/2010 8:44:14 AM 8158681
32 23/04/2010 9:54:46 AM 8158686
33 Truck: Truck 3 Twin Steer
34 Hauler: abc cartage
35 18/04/2010 6:55:47 PM 8158422
36 18/04/2010 9:16:33 PM 8158445
37 28/04/2010 7:14:36 PM 8158768
38 28/04/2010 9:40:02 PM 8158789
39 28/04/2010 11:11:36 PM 8158801
40 13/04/2010 8:38:29 PM 8158322
41 13/04/2010 10:33:47 PM 8158328
42 14/04/2010 12:41:31 AM 8158338
43 14/04/2010 2:47:36 AM 8158349
44 15/04/2010 8:22:52 PM 8158361
45 15/04/2010 9:17:26 PM 8158369
46 15/04/2010 10:39:09 PM 8158378
47 16/04/2010 12:41:49 AM 8158388
48 27/04/2010 7:44:39 PM 8158690
49 27/04/2010 8:41:57 PM 8158700
50 27/04/2010 11:17:02 PM 8158721
51 28/04/2010 1:32:22 AM 8158730
52 28/04/2010 2:56:20 AM 8158746
53 19/04/2010 11:33:57 PM 8158505
54 20/04/2010 12:30:09 AM 8158520
55 20/04/2010 1:38:54 AM 8158535
56 Truck: Truck 4 Twin Steer
57 Hauler: abc cartage
58 18/04/2010 6:37:37 PM 8158421
59 18/04/2010 9:13:14 PM 8158444
60 19/04/2010 12:19:43 AM 8158457
61 13/04/2010 8:35:46 PM 8158321
62 13/04/2010 10:24:09 PM 8158327
63 14/04/2010 12:13:14 AM 8158337
64 14/04/2010 2:41:11 AM 8158346
65 14/04/2010 3:57:05 AM 8158355
66 15/04/2010 8:02:25 PM 8158360
67 15/04/2010 9:10:35 PM 8158367
68 15/04/2010 10:35:45 PM 8158377
69 16/04/2010 12:38:53 AM 8158387
70 Truck: Truck 5 Tandum
71 Hauler: abc cartage
72 27/04/2010 9:49:50 PM 8158711
73 28/04/2010 1:51:29 AM 8158732
74 Truck: Truck 6 Tandum
75 Hauler: abc cartage
76 27/04/2010 8:44:36 PM 8158701
77 27/04/2010 10:12:23 PM 8158714
78 28/04/2010 2:49:08 AM 8158745


So for example:-

I want to know how many of these trucks worked for each date in April 2010...

Something like:

Date No of trucks
1/4 4
2/4 6
3/4 4
4/4 5

Can anyone offer some advice?

Cheers,
Nick

 

[Robert Mika]

If your data are in cells A1:D78 and dates are in A81 down enter this in B81 and copy down:
=COUNTIF($B$1:$B$80,A81)

 

[Aladin Akyurek]

Something like...

Control+shift+enter, not just enter:

=SUM(IF(INT(DateRange)=Date,IF(1-(VehicleRange=""),1)))

This formula would count the same vehicle multiple times if it was active on the same date multiple times.