Counting distinct values as well as giving a sum

davey4444

Board Regular
Joined
Nov 16, 2010
Messages
93
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
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:

davey4444

Board Regular
Joined
Nov 16, 2010
Messages
93
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.
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
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?
 

davey4444

Board Regular
Joined
Nov 16, 2010
Messages
93
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.
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
OK that's fine, have a great day hope you can figure it out with Access :)
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
325
Office Version
365, 2016
Platform
Windows
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]
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,626
Office Version
2013
Platform
Windows
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]
 

petertenthije

Board Regular
Joined
Sep 25, 2012
Messages
187
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));
 

petertenthije

Board Regular
Joined
Sep 25, 2012
Messages
187
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);
 

Watch MrExcel Video

Forum statistics

Threads
1,095,168
Messages
5,442,796
Members
405,197
Latest member
queryashish

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top