Transactions Per Hour

sa7toot

New Member
Joined
Jan 6, 2014
Messages
35
Hi all,

I want to run a query which identifies the number of transactions conducted per account per hour and the amount. my table have 3 columns
A: Account Number
B: Transaction date & time
C: Transaction Amount

I have searched the forum q's but didn't see anything relevant so far. So, appreciate your help.

thnx
Sa7toot
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Can you post some sample data, so we can check it out for you.
 
Upvote 0
Unless you are specifically looking for a VBA programming solution (which I suspect you are), then getting familiar with COUNTIFS, SUMIFS and the HOUR function would seem to be in your future, as they could do it relatively easily in my view.

An indication of the size of your listing will also help, as again I suspect that it is going to be quite long

Cheers

shane
 
Upvote 0
A/C
Date & Time
Amout
1

31/07/2019 00:08:31

<tbody>
</tbody>
5
2

31/07/2019 00:09:16

<tbody>
</tbody>
10
3

31/07/2019 00:19:51

<tbody>
</tbody>
5
1

31/07/2019 00:39:50

<tbody>
</tbody>
10
1

31/07/2019 00:49:08

<tbody>
</tbody>
10
3

31/07/2019 00:50:10

<tbody>
</tbody>
55
2

31/07/2019 01:27:16

<tbody>
</tbody>
15
1

31/07/2019 03:59:20

<tbody>
</tbody>
20
1

31/07/2019 04:00:14

<tbody>
</tbody>
100
2

31/07/2019 06:39:54

<tbody>
</tbody>
105

<tbody>
</tbody>
Hello,

Below is a sample of the data, in any particular day, there are around 100K to 200K records to review. I am trying with COUNTIFs and SUMIFs but so far failing.
 
Upvote 0
I'm about to finish for the day, but a few quick questions,

Is the time in 24 hr format?
Are the hour increments across the full 24 hr day?
Do you want a full report, I.e. every account, every hour, or do you just want to examine an account and an hour I.e selectable with drop down lists?
Does the list have multiple days, and if so over what range?

Cheers

shane
 
Upvote 0
Hello Shane,

Yes, the time is in 2 hours format.
Yes the our increments the full 24 hr day.
I would want the output to show a segregation of transactions by count. for example, how many accounts done 1 in an hour or 2 or 3 or 4 or 5 & more. maximum segregation is 5 and whatever exceeds is in "more than 5" row .
the list will be run on a rolling period, it could contain days.
 
Upvote 0
You mean like this.


Excel 2013/2016
ABCDEFGH
3DateID0-11-23-44-56-7Total
431-07-201913115
531-07-201921113
631-07-2019322
Sheet11


Muz
 
Last edited:
Upvote 0
Muz can you post your formula for sa7toot as well pls mate?
 
Upvote 0
@ RasGhul, I did Pivot after little bit changes in his frame, just to see how he want the result.
 
Upvote 0
@RasGhul,
Well yes, sort of. Basically, I am looking to show what is the number of customers who do more than 3 transactions per hour in a day.

thanks :)
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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