count number of unique records for each date

nish1585

New Member
Joined
Apr 30, 2010
Messages
2
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,167
Messages
5,570,598
Members
412,330
Latest member
carlosjw
Top