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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Something like this should work.
Excel Formula: 
=SUMIFS(Sheet1!G:G,Sheet1!A:A,">="&C3,Sheet1!A:A,"<="&D3,Sheet1!C:C,B3,Sheet1!I:I,">0")
Note that due to the way times are processed it is likely that some rows where column A is an exact match for the start or end time on sheet 2 could be missed in the result.

A more robust formula could be possible but would be extremely inefficient with the volume of data and your version of excel.
 
Upvote 0
jasonb75 said:
Something like this should work.
Excel Formula: 
=SUMIFS(Sheet1!G:G,Sheet1!A:A,">="&C3,Sheet1!A:A,"<="&D3,Sheet1!C:C,B3,Sheet1!I:I,">0")
Note that due to the way times are processed it is likely that some rows where column A is an exact match for the start or end time on sheet 2 could be missed in the result.

A more robust formula could be possible but would be extremely inefficient with the volume of data and your version of excel.
Click to expand...
I appreciate the quick response, but I still can't figure it out. Does this SUMIFS go in Sheet2 cell E3 and return a sum from the Amount column?

I also need a way to pull the corresponding value from the Location column, and the Date column for the first and last date that correspond with the cells pulled from the Amount column.
 
Upvote 0
I see what you mean now, I had misread the question and thought that you were entering the other columns manually and looking for a result based on that.

Is your profile up to date showing Excel 2016 as your current version, or are you now using something more recent?
 
Upvote 0
I assume in your sample data C43 and J43 have false data. C43 must be "AA1103" and J43 must be nighttime. Otherwise, it makes no sense in the end. AA1104 starts before AA1103 has an end. Also, column G will not give you the sum 282 (Sheet2 E7) unless you don't make the changes as I said.
Anyway, your formulas are listed below.
Sheet2 B3:
Excel Formula: 
=IFERROR(INDEX(Sheet1!$C$2:$C$44,MATCH(Sheet1!O2,Sheet1!$J$2:$J$44,0)),"")
Sheet2 C3:
This is an array formula. Paste the formula and press Ctrl+Shift+Enter together.
Excel Formula: 
=IFERROR(INDEX(Sheet1!$J$1:$J$44,SMALL(IF(((OFFSET(Sheet1!$I$2:$I$44,-1,0)<>"")*(Sheet1!$I$2:$I$44=""))>0,(ROW(Sheet1!$H$2:$H$44))),ROWS(Sheet1!$N$1:$N1))),"")
Sheet2 D3:
This is an array formula. Paste the formula and press Ctrl+Shift+Enter together.
Excel Formula: 
=IFERROR(INDEX(Sheet1!$J$1:$J$44,SMALL(IF(((OFFSET(Sheet1!$I$2:$I$44,1,0)<>"")*(Sheet1!$I$2:$I$44=""))>0,(ROW(Sheet1!$H$2:$H$44))),ROWS(Sheet1!$N$1:$N1))),"")
Sheet2 E3:
Excel Formula: 
=SUMIFS(Sheet1!$G$2:$G$44,Sheet1!$C$2:$C$44,Sheet1!$N2,Sheet1!$J$2:$J$44,">="&Sheet1!$O2,Sheet1!$J$2:$J$44,"<="&Sheet1!$P2)
 
Upvote 0
A small revision to these two formulas. My own draft referances left there.
Sheet2 B3:
Excel Formula: 
=IFERROR(INDEX(Sheet1!$C$2:$C$44,MATCH($C3,Sheet1!$J$2:$J$44,0)),"")
Sheet2 E3:
Excel Formula: 
=SUMIFS(Sheet1!$G$2:$G$44,Sheet1!$C$2:$C$44,$B3,Sheet1!$J$2:$J$44,">="&$C3,Sheet1!$J$2:$J$44,"<="&$D3)
 
Upvote 0
Flashbond said:
I assume in your sample data C43 and J43 have false data. C43 must be "AA1103" and J43 must be nighttime. Otherwise, it makes no sense in the end. AA1104 starts before AA1103 has an end. Also, column G will not give you the sum 282 (Sheet2 E7) unless you don't make the changes as I said.
Click to expand...
Based on the question and the sample of source data, I think that the error is more likely to be in the expected results and that the actual results should be more like this.
I haven't given any thought of how to do this with 2016 compatible formulas yet (if required) but I imagine an aggregate array should work.

Johnny00.xlsx
BCDE
2LocationStart DateEnd DateAmount
3AA110111/08/2022 20:01:3011/08/2022 20:40:27145
4AA110111/08/2022 21:27:1811/08/2022 23:37:3382
5AA110211/08/2022 20:41:2711/08/2022 20:41:27100
6AA110311/08/2022 18:26:4911/08/2022 19:05:35480
7AA110311/08/2022 19:57:4811/08/2022 23:10:00182
8AA110411/08/2022 18:27:3911/08/2022 18:27:39100
Sheet2
Cell Formulas
RangeFormula
B3:B8B3=FILTER(Sheet1!C2:C44,(Sheet1!I2:I44="")*((Sheet1!I1:I43<>"")+(Sheet1!C2:C44<>Sheet1!C1:C43)),"")
C3:C8C3=FILTER(Sheet1!A2:A44,(Sheet1!I2:I44="")*((Sheet1!I1:I43<>"")+(Sheet1!C2:C44<>Sheet1!C1:C43)),"")
D3:D8D3=FILTER(Sheet1!A2:A44,(Sheet1!I2:I44="")*((Sheet1!I3:I45<>"")+(Sheet1!C2:C44<>Sheet1!C3:C45)),"")
E3:E8E3=SUMIFS(Sheet1!G2:G44,Sheet1!C2:C44,B3#,Sheet1!A2:A44,">="&C3#,Sheet1!A2:A44,"<="&D3#)
Dynamic array formulas.
 
Upvote 0
Following up on my previous post, I've reconfigured the formulas to be excel 2016 compatible. When adjusting to your actual data note that the ranges used are not all exactly the same (this applies to the formulas in both posts).
Where I have used row 2 to row 44, this range should look at the data only.
Where I have used row 1 to row 43, this should include the header row at the top and exclude the last row of data.
Where I have used row 3 to row 45, this should exclude the first row of data and include the first empty row below the data.

Offsetting arrays in this way allows for a row in the table to be compared to whatever is directly above, or below. This makes it easier to extract the correct records in line with your criteria.

Johnny00.xlsx
BCDE
2LocationStart DateEnd DateAmount
3AA110111/08/2022 20:01:3011/08/2022 20:40:27145
4AA110111/08/2022 21:27:1811/08/2022 23:37:3382
5AA110211/08/2022 20:41:2711/08/2022 20:41:27100
6AA110311/08/2022 18:26:4911/08/2022 19:05:35480
7AA110311/08/2022 19:57:4811/08/2022 23:10:00182
8AA110411/08/2022 18:27:3911/08/2022 18:27:39100
Sheet2
Cell Formulas
RangeFormula
B3:B8B3=IF(B2="","",IFERROR(INDEX(Sheet1!$C$2:$C$44,AGGREGATE(15,6,ROW(Sheet1!$C$2:$C$44)/(Sheet1!$I$2:$I$44="")/(((Sheet1!$I$1:$I$43<>"")+(Sheet1!$C$2:$C$44<>Sheet1!$C$1:$C$43))>0),ROWS(B$3:B3))-ROW(Sheet1!$C$2)+1),""))
C3:C8C3=IF(B3="","",INDEX(Sheet1!$A$2:$A$44,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$44)/(Sheet1!$I$2:$I$44="")/(((Sheet1!$I$1:$I$43<>"")+(Sheet1!$C$2:$C$44<>Sheet1!$C$1:$C$43))>0),ROWS(C$3:C3))-ROW(Sheet1!$A$2)+1))
D3:D8D3=IF(B3="","",INDEX(Sheet1!$A$2:$A$44,AGGREGATE(15,6,ROW(Sheet1!$C$2:$C$44)/(Sheet1!$I$2:$I$44="")/(((Sheet1!$I$3:$I$45<>"")+(Sheet1!$C$2:$C$44<>Sheet1!$C$3:$C$45))>0),ROWS(D$3:D3))-ROW(Sheet1!$A$2)+1))
E3:E8E3=IF(B3="","",SUMIFS(Sheet1!$G$2:$G$44,Sheet1!$C$2:$C$44,B3,Sheet1!$A$2:$A$44,">="&C3,Sheet1!$A$2:$A$44,"<="&D3))
 
Upvote 0
jasonb75 said:
Following up on my previous post, I've reconfigured the formulas to be excel 2016 compatible. When adjusting to your actual data note that the ranges used are not all exactly the same (this applies to the formulas in both posts).
Where I have used row 2 to row 44, this range should look at the data only.
Where I have used row 1 to row 43, this should include the header row at the top and exclude the last row of data.
Where I have used row 3 to row 45, this should exclude the first row of data and include the first empty row below the data.

Offsetting arrays in this way allows for a row in the table to be compared to whatever is directly above, or below. This makes it easier to extract the correct records in line with your criteria.

Johnny00.xlsx
BCDE
2LocationStart DateEnd DateAmount
3AA110111/08/2022 20:01:3011/08/2022 20:40:27145
4AA110111/08/2022 21:27:1811/08/2022 23:37:3382
5AA110211/08/2022 20:41:2711/08/2022 20:41:27100
6AA110311/08/2022 18:26:4911/08/2022 19:05:35480
7AA110311/08/2022 19:57:4811/08/2022 23:10:00182
8AA110411/08/2022 18:27:3911/08/2022 18:27:39100
Sheet2
Cell Formulas
RangeFormula
B3:B8B3=IF(B2="","",IFERROR(INDEX(Sheet1!$C$2:$C$44,AGGREGATE(15,6,ROW(Sheet1!$C$2:$C$44)/(Sheet1!$I$2:$I$44="")/(((Sheet1!$I$1:$I$43<>"")+(Sheet1!$C$2:$C$44<>Sheet1!$C$1:$C$43))>0),ROWS(B$3:B3))-ROW(Sheet1!$C$2)+1),""))
C3:C8C3=IF(B3="","",INDEX(Sheet1!$A$2:$A$44,AGGREGATE(15,6,ROW(Sheet1!$A$2:$A$44)/(Sheet1!$I$2:$I$44="")/(((Sheet1!$I$1:$I$43<>"")+(Sheet1!$C$2:$C$44<>Sheet1!$C$1:$C$43))>0),ROWS(C$3:C3))-ROW(Sheet1!$A$2)+1))
D3:D8D3=IF(B3="","",INDEX(Sheet1!$A$2:$A$44,AGGREGATE(15,6,ROW(Sheet1!$C$2:$C$44)/(Sheet1!$I$2:$I$44="")/(((Sheet1!$I$3:$I$45<>"")+(Sheet1!$C$2:$C$44<>Sheet1!$C$3:$C$45))>0),ROWS(D$3:D3))-ROW(Sheet1!$A$2)+1))
E3:E8E3=IF(B3="","",SUMIFS(Sheet1!$G$2:$G$44,Sheet1!$C$2:$C$44,B3,Sheet1!$A$2:$A$44,">="&C3,Sheet1!$A$2:$A$44,"<="&D3))
Click to expand...
Wow bravo. I was not able to get single empty row 20.:41:29 with AGGREGATE.
Edit: Oh, I see you neither.
 
Upvote 0
Flashbond said:
I was not able to get single empty row 20.:41:29
Click to expand...
I think that you were looking at the wrong date / time column. The OP's results matched to the timestamps in column A while you were using column J. Row 5 in the mini sheets that I have posted is the row with 20:41:29 that you mention (there is a difference of 1-2 seconds between column A and J for most of the entries).
 
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
141
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
128
Abdo
A
P
  • Solved
Choose Max Date
Replies
7
Views
480
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,215,059
Messages
6,122,913
Members
449,093
Latest member
dbomb1414

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