How to sum specific rows and record the first and last row

J

Johnny00

New Member
Joined
Oct 1, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
This one is difficult to explain, so I attached a sheet showing the result I would like and the second sheet is the data that I started with. The real sheet I am working with is 100k+ rows, not sure if that changes your suggestions. I am looking for a sum of the Amount for the periods without an ID#. The result needs to show the Location, beginning and ending Date, and the total Amount for that period.


Example A.xlsx
BCDE
2LocationStart DateEnd DateAmount
3AA110111/8/2022 20:01:3011/8/2022 20:40:27145
4AA110111/8/2022 21:27:1811/8/2022 23:37:3382
5AA110211/8/2022 20:41:2711/8/2022 20:41:27100
6AA110311/8/2022 18:26:4911/8/2022 19:05:35480
7AA110311/8/2022 19:57:4811/8/2022 18:27:39282
Sheet2


Example A.xlsx
ABCDEFGHIJ
1DateMachineLocationEventCodeEventBonus IDAmountStaffID #Insert Date
211/8/2022 18:24:01102037AA110113284700Bill inserted0-0201.23E+191483056011/8/2022 18:24:02
311/8/2022 18:38:51102037AA110113284700Bill inserted0-0201.23E+191483056011/8/2022 18:38:53
411/8/2022 19:43:34102037AA110113284700Bill inserted0-0201.91E+191486381311/8/2022 19:43:35
511/8/2022 20:01:30102037AA110113284700Bill inserted0-02011/8/2022 20:01:31
611/8/2022 20:16:26102037AA110113284700Bill inserted0-02011/8/2022 20:16:28
711/8/2022 20:36:27102037AA110113284700Bill inserted0-0511/8/2022 20:36:28
811/8/2022 20:40:27102037AA110113284700Bill inserted0-010011/8/2022 20:40:29
911/8/2022 20:55:18102037AA110113284700Bill inserted0-0201.23E+191536657711/8/2022 20:55:20
1011/8/2022 21:27:18102037AA110113284700Bill inserted0-02011/8/2022 21:27:19
1111/8/2022 21:43:11102037AA110113284700Bill inserted0-02011/8/2022 21:43:13
1211/8/2022 22:33:42102037AA110113284700Bill inserted0-02011/8/2022 22:33:43
1311/8/2022 23:05:32102037AA110113284700Bill inserted0-0111/8/2022 23:05:34
1411/8/2022 23:05:39102037AA110113284700Bill inserted0-0111/8/2022 23:05:41
1511/8/2022 23:37:33102037AA110113284700Bill inserted0-02011/8/2022 23:37:34
1611/8/2022 19:06:06101152AA110213284700Bill inserted0-0201.23E+191483056011/8/2022 19:06:08
1711/8/2022 19:08:59101152AA110213284700Bill inserted0-0201.23E+191483056011/8/2022 19:09:02
1811/8/2022 19:12:23101152AA110213284700Bill inserted0-0201.23E+191483056011/8/2022 19:12:25
1911/8/2022 20:41:27101152AA110213284700Bill inserted0-010011/8/2022 20:41:29
2011/8/2022 21:03:46101152AA110213284700Bill inserted0-01001.91E+191320113411/8/2022 21:03:48
2111/8/2022 21:09:02101152AA110213284700Bill inserted0-0201.91E+191320113411/8/2022 21:09:04
2211/8/2022 21:09:08101152AA110213284700Bill inserted0-0201.91E+191320113411/8/2022 21:09:10
2311/8/2022 21:09:11101152AA110213284700Bill inserted0-0201.91E+191320113411/8/2022 21:09:14
2411/8/2022 18:00:09100597AA110313284700Bill inserted0-01001.23E+19245424511/8/2022 18:00:11
2511/8/2022 18:26:49100597AA110313284700Bill inserted0-02011/8/2022 18:26:50
2611/8/2022 18:26:51100597AA110313284700Bill inserted0-02011/8/2022 18:26:52
2711/8/2022 18:29:19100597AA110313284700Bill inserted0-010011/8/2022 18:29:20
2811/8/2022 18:31:09100597AA110313284700Bill inserted0-010011/8/2022 18:31:11
2911/8/2022 18:33:23100597AA110313284700Bill inserted0-010011/8/2022 18:33:24
3011/8/2022 18:36:38100597AA110313284700Bill inserted0-010011/8/2022 18:36:39
3111/8/2022 18:59:44100597AA110313284700Bill inserted0-02011/8/2022 18:59:45
3211/8/2022 19:05:35100597AA110313284700Bill inserted0-02011/8/2022 19:05:36
3311/8/2022 19:23:35100597AA110313284700Bill inserted0-0201.91E+191302213111/8/2022 19:23:37
3411/8/2022 19:57:48100597AA110313284700Bill inserted0-010011/8/2022 19:57:49
3511/8/2022 19:57:53100597AA110313284700Bill inserted0-01011/8/2022 19:57:54
3611/8/2022 19:57:56100597AA110313284700Bill inserted0-0511/8/2022 19:57:57
3711/8/2022 21:15:43100597AA110313284700Bill inserted0-02011/8/2022 21:15:44
3811/8/2022 21:30:00100597AA110313284700Bill inserted0-0511/8/2022 21:30:02
3911/8/2022 21:31:05100597AA110313284700Bill inserted0-02011/8/2022 21:31:07
4011/8/2022 23:09:38100597AA110313284700Bill inserted0-0111/8/2022 23:09:39
4111/8/2022 23:09:46100597AA110313284700Bill inserted0-0111/8/2022 23:09:48
4211/8/2022 23:10:00100597AA110313284700Bill inserted0-02011/8/2022 23:10:01
4311/8/2022 18:27:39102939AA110413284700Bill inserted0-010011/8/2022 18:27:40
4411/8/2022 18:45:10102939AA110413284700Bill inserted0-01001.91E+191449550311/8/2022 18:45:12
Sheet1
 
Sort by date Sort by votes
Oh, I am sorry. Actually you are right. I setted up all my formulas according to INSERT column. So, again bravo to include that one empty row 👏 I was not able to managed to get it with AGGREGATE somehow... I will bookmark your answer to use in the future.

By the way, the post owner doesn't seem to be enthusiastic as we are...
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Flashbond said:
the post owner doesn't seem to be enthusiastic as we are...
Click to expand...
I was thinking the same and wondering if they got the answer they needed elsewhere, however it was an interesting challenge so I wanted to have a go at it even if it is no longer needed.

Did you notice the difference between the last row of your results and the last 2 rows of mine? I was looking at your formulas to begin with, trying to get the same result but it kept going wrong, that's why I went to the Filter method, then expanded from it.
 
Upvote 0
Yeah I understand you because I felt the same. It was a quite challange for me also. I can't play around with filter function since I am using XL2019. The dataset still doesn't make any sense to me at the very end.
I don't remember my results exactly but at the last row I got 0 sum for A1103 because I couldn't figure it out how to relate it with an ending time. I got 282 immediately after I altered A1104 to A1103 in row 43, which is the desired result. Also the time has to be adjusted according to an ending hour.
I got no A1104 in my list because I was not able to lacate any start time for it.
Either way, it was nice to fiddle around 👍
 
Upvote 0
jasonb75 and Flashbond, I appreciate all of your work on my problem. I got caught up with a bunch of other issues at work. Now just getting back to working on this. I read through your responses and tried the suggested solutions, but I think my original data had too many unnecessary columns. I have paired it down a little to better explain what I'm trying to do.

The example data is from 3 slot machines (AA1203, AA1301, and AA1302) and it shows the type of bill (US currency) inserted along with 'tickets" that have a monetary value listed in the Amount column. The last column ID# shows the gambler's rewards card number if they have it inserted into the machine while putting the money in.

I am trying to create a report that is identifies periods of time where an individual machine has no rewards card inserted but money and/or tickets are being inserted. I am not concerned with any activity while a rewards card is inserted, but I need the rewards card data to show when the last card was taken out and then next card inserted. The goal is to record the data between rewards cards for a given machine.

In the example data below, I am looking for the green, blue, and orange to each return separate answers. The green should show $21.54 between 12/3/22 00:06:26 and 12/3/22 00:11:17. Blue should show $165.90 between 12/3/22 00:43:45 and 12/3/22 04:27:14. The orange should show $20 between 12/3/22 08:52:44 and 12/3/22 08:52:44.

12012022 Bills and Tickets Inserted.xlsx
ABCDEF
1DateMachineLocationEventAmountID #
212/03/2022 10:19:11102081AA1203Bill inserted20
312/03/2022 10:29:04102081AA1203Ticket redeemed1.45
412/03/2022 11:03:24102081AA1203Ticket redeemed200.08
512/03/2022 11:03:40102081AA1203Ticket redeemed0.22
612/03/2022 11:28:12102081AA1203Ticket redeemed42.85
712/03/2022 11:50:47102081AA1203Bill inserted100
812/03/2022 11:58:13102081AA1203Ticket redeemed13.0514899215
912/03/2022 00:06:26102624AA1301Ticket redeemed13.65
1012/03/2022 00:09:03102624AA1301Ticket redeemed7.31
1112/03/2022 00:11:13102624AA1301Ticket redeemed0.33
1212/03/2022 00:11:17102624AA1301Ticket redeemed0.25
1312/03/2022 00:16:28102624AA1301Ticket redeemed51.215374270
1412/03/2022 00:43:45102624AA1301Bill inserted1
1512/03/2022 00:44:49102624AA1301Ticket redeemed60.4
1612/03/2022 00:49:10102624AA1301Bill inserted100
1712/03/2022 04:27:14102624AA1301Ticket redeemed4.5
1812/03/2022 04:27:51102624AA1301Bill inserted5014891814
1912/03/2022 08:52:44102624AA1301Bill inserted20
2012/03/2022 12:29:27102624AA1301Bill inserted10014915450
2112/03/2022 00:00:11102249AA1302Ticket redeemed12.73
2212/03/2022 00:04:42102249AA1302Ticket redeemed0.22
2312/03/2022 00:04:53102249AA1302Bill inserted1
2412/03/2022 00:05:00102249AA1302Bill inserted1
2512/03/2022 00:05:06102249AA1302Bill inserted1
2612/03/2022 00:05:15102249AA1302Bill inserted1
Sheet6
 
Upvote 0
Will not be able to find the last date cuz there's no ID in the last row, ignore the column G it's for testing stuff


J2: =INDEX($A$2:$A26,AGGREGATE(15,6,(ROW($F$2:$F$26)-1)/--(($F$2:$F$26<>$F$1:$F$26)*($F$2:$F$26="")),ROWS($J$2:J2)))
K2: =INDEX($A$2:$A$26,AGGREGATE(15,6,(ROW($F$2:$F$26)-1)/--(($F$2:$F$26<>$F$3:$F$27)*($F$2:$F$26="")),ROWS($J$2:J2)))
L2: =SUM(INDEX($E$2:$E$26,MATCH(J2,$A$2:$A$26,0)):INDEX($E$2:$E$26,MATCH(K2,$A$2:$A$26,0)))
 

Attachments

  • jit.PNG
    jit.PNG
    89.8 KB · Views: 7
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Search for topics based on their groups.
Replies
7
Views
140
Peter_SSs
Peter_SSs
A
  • Question
code to copy last row & exclude zero value of the data in last row for each name
Replies
0
Views
127
Abdo
A
P
  • Solved
Choose Max Date
Replies
7
Views
479
Joe4
Joe4
G
  • Question
Formula to Retrieve uniqueID row Above
Replies
1
Views
352
Trebor76
T
F
  • Question
Counting when record falls within a dynamically updating date range, additional matching criteria
Replies
1
Views
353
awoohaw
awoohaw

Forum statistics

Threads
1,214,990
Messages
6,122,625
Members
449,093
Latest member
catterz66

Share this page

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