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
 

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,138
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.
 

Forum statistics

Threads
1,082,114
Messages
5,363,244
Members
400,722
Latest member
DrewPop24

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top