Counting distinct values as well as giving a sum

davey4444

Board Regular
Joined
Nov 16, 2010
Messages
97
Hello,

I am after some assistance in getting some information out of my database which is causing me some problems.
My data shows each individual journey that a truck has taken and I am trying to show the number of journeys each truck did in a given county on a given day and a given shift. I've given an example of the data below as well as the desired output. My understanding with vba is limited and have mostly used queries in Access so I'm a little stuck here but am more than willing to learn so any help is gratefully received.

Thanks.

Book1
ABCDE
1Data
2
3CountyDateShiftRegistrationUse
4Cornwall01/04/2020DAYBCA478FALSE
5Cornwall01/04/2020DAYDDD543TRUE
6Cornwall01/04/2020DAYXYZ999TRUE
7Cornwall01/04/2020NIGHTDDD543TRUE
8Cornwall01/05/2020DAYDDD543TRUE
9Cornwall01/05/2020DAYDDD543TRUE
10Cornwall01/05/2020DAYDDD543TRUE
11Cornwall01/05/2020NIGHTAAA111TRUE
12Cornwall01/05/2020NIGHTABC123TRUE
13Devon01/04/2020DAYABC123TRUE
14Devon01/04/2020DAYXYZ999TRUE
15Devon01/04/2020NIGHTABC123FALSE
16Devon01/04/2020NIGHTABC123TRUE
17Devon01/04/2020NIGHTBCA478TRUE
18Devon01/04/2020NIGHTXYZ999TRUE
19Devon01/05/2020DAYABC123TRUE
20Devon01/05/2020DAYBCA478TRUE
21Devon01/05/2020NIGHTAAA111TRUE
22Devon01/05/2020NIGHTAAA111TRUE
23Devon01/05/2020NIGHTABC123TRUE
24Devon01/05/2020NIGHTABC123TRUE
25Devon01/05/2020NIGHTABC123TRUE
26
27
28Output
29
30CountyDateShiftRegistrationJobs per county, day, shift
31Cornwall01/04/2020DAYDDD5431
32Cornwall01/04/2020DAYXYZ9991
33Cornwall01/04/2020NIGHTDDD5431
34Cornwall01/05/2020DAYDDD5433
35Cornwall01/05/2020NIGHTAAA1111
36Cornwall01/05/2020NIGHTABC1231
37Devon01/04/2020DAYABC1231
38Devon01/04/2020DAYXYZ9991
39Devon01/04/2020NIGHTABC1231
40Devon01/04/2020NIGHTBCA4781
41Devon01/04/2020NIGHTXYZ9991
42Devon01/05/2020DAYABC1231
43Devon01/05/2020DAYBCA4781
44Devon01/05/2020NIGHTAAA1112
45Devon01/05/2020NIGHTABC1233
Sheet1
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Hey there, do you have O365?

If so then this can be achieved using FILTER and UNIQUE dynamic array functions.

So with the headers in row 30 (County, Date, Shift, Reg, Jobs per county, day, shift)
Go to cell A31:

=UNIQUE(FILTER(A3:D25,E3:E25=TRUE))

Then in cell E31:
=SUMPRODUCT((A31=$A$4:$A$25)*(B31=$B$4:$B$25)*(C31=$C$4:$C$25)*(D31=$D$4:$D$25)*(TRUE=$E$4:$E$25))

and drag this down to E45.
 
Last edited:
Upvote 0
Hey there, do you have O365?

If so then this can be achieved using FILTER and UNIQUE dynamic array functions.

So with the headers in row 30 (County, Date, Shift, Reg, Jobs per county, day, shift)
Go to cell A31:

=UNIQUE(FILTER(A3:D25,E3:E25=TRUE))

Then in cell E31:
=SUMPRODUCT((A31=$A$4:$A$25)*(B31=$B$4:$B$25)*(C31=$C$4:$C$25)*(D31=$D$4:$D$25)*(TRUE=$E$4:$E$25))

and drag this down to E45.
Hi, thanks for the reply. I do have O365 but it seems that the UNIQUE function is only available on the monthly channel and my company uses the semi-annual one so I'm unable to use it. Also, I think that due to the number of records I have, it's necessary to use Access for this which is a shame as I'm far happier in Excel.
 
Upvote 0
Oh I see! Hmm, the same can be achieved using INDEX AGGREGATE combinations however, but if you're going to do it in Access that's fine. Do you want me to give you a solution with Excel still anyway or are you set on using Access?
 
Upvote 0
Unfortunately I think that I will have to settle with Access as I'm going to be dealing with more than 100k records but thanks so much for replying.

I've realised that my 'desired output' is actually not right and that I'd like a level up from this so just for example just showing the number of unique registrations used in Devon on 01/05 at night and then the number of journeys, so in this case it would be 2 different registrations and 5 journeys.
 
Upvote 0
OK that's fine, have a great day hope you can figure it out with Access :)
 
Upvote 0
Can you tell us what the USE field is for and how it plays into getting your expected output?

What you are asking for is quite easy with Access using a SQL aggregate query.

Ignoring the USE field for now, create a new query, switch to SQL view, and paste this (rename [Data] to match your table name and any of the other fields as necessary...):

SQL:
SELECT
    [County],
    [Date],
    [Shift],
    [Registration],
    Count(*) AS [Jobs per county, day, shift]
FROM
    [Data]
GROUP BY
    [County],
    [Date],
    [Shift],
    [Registration]
 
Upvote 0
Note that assuming Use means use only the ones with true in the use column, then just add a where condition:

VBA Code:
SELECT
    [County],
    [Date],
    [Shift],
    [Registration],
    Count(*) AS [Jobs per county, day, shift]
FROM
    [Data]
WHERE
    [Use] = -1
GROUP BY
    [County],
    [Date],
    [Shift],
    [Registration]
 
Upvote 0
Why are you using VBA, when a query would also get the desired results?

SELECT Table1.County, Table1.Date, Table1.Shift, Table1.registration, Count(Table1.Use) AS [Jobs per county, day, shift]
FROM Table1
GROUP BY Table1.County, Table1.Date, Table1.Shift, Table1.registration, Table1.Use
HAVING (((Table1.Use)=True));
 
Upvote 0
I've realised that my 'desired output' is actually not right and that I'd like a level up from this so just for example just showing the number of unique registrations used in Devon on 01/05 at night and then the number of journeys, so in this case it would be 2 different registrations and 5 journeys.
In that case I would use four queries.

Query 1 makes a list with unique combinations of county, date, shift and registrations
Query 2 uses the result of query 1, but counts the number of unique registrations
Query 3 counts the number of journeys
Query 4 combines the results of query 2 and query 3.


Query1_ registrations
SELECT DISTINCTROW Table1.County, Table1.Date, Table1.Shift, Table1.registration
FROM Table1
GROUP BY Table1.County, Table1.Date, Table1.Shift, Table1.registration, Table1.Use
HAVING (((Table1.Use)=True));

Query2_ registrations
SELECT DISTINCTROW [Query1_ registrations].County, [Query1_ registrations].Date, [Query1_ registrations].Shift, Count([Query1_ registrations].registration) AS Registrations
FROM [Query1_ registrations]
GROUP BY [Query1_ registrations].County, [Query1_ registrations].Date, [Query1_ registrations].Shift;

Query3_journeys
SELECT DISTINCT Table1.County, Table1.Date, Table1.Shift, Count(Table1.registration) AS Journeys
FROM Table1
GROUP BY Table1.County, Table1.Date, Table1.Shift, Table1.use
HAVING (((Table1.use)=True));

Query4_combination
SELECT [Query2_ registrations].County, [Query2_ registrations].Date, [Query2_ registrations].Shift, [Query2_ registrations].Registrations, Query3_journeys.Journeys
FROM [Query2_ registrations] INNER JOIN Query3_journeys ON ([Query2_ registrations].Shift = Query3_journeys.Shift) AND ([Query2_ registrations].Date = Query3_journeys.Date) AND ([Query2_ registrations].County = Query3_journeys.County);
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top