Sumifs Maybe?

[rehberger]

Hi, if i have hundreds of lines all out of order that are set up like the first table; is there a way to sum the data by trips? A trip being consecutive days.

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Acct#[/TD]
[TD]Date[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]3005[/TD]
[TD]10/24/18[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]3005[/TD]
[TD]10/25/18[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3038[/TD]
[TD]9/1/18[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]3038[/TD]
[TD]9/2/18[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]3005[/TD]
[TD]11/13/18[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]3005[/TD]
[TD]11/14/18[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]3038[/TD]
[TD]10/1/18[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]3038[/TD]
[TD]10/2/18[/TD]
[TD]75[/TD]
[/TR]
</tbody>[/TABLE]

Results with formulas in red highlighted cells
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Acct#[/TD]
[TD]Trip 1[/TD]
[TD]Trip 2[/TD]
[TD]Trip 3[/TD]
[/TR]
[TR]
[TD]3005[/TD]
[TD]150[/TD]
[TD]75[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3038[/TD]
[TD]75[/TD]
[TD]125[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks for any help!

 

[CyrusTheVirus]

Are you able to sort the data at all? Like sort by Acct# then Date? Because if you can, then add a helper column in column D and input the below formula, then pivot off of that and put the helper column in the columns section of the pivot table.

=IF(A2<>A1,1,IF(AND(A2=A1,(B2-1)<>B1),D1+1,D1))

Doing that gives me the below result.

[TABLE="width: 254"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Acct#[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD="align: right"]3005[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]225[/TD]
[/TR]
[TR]
[TD="align: right"]3038[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]425[/TD]
[/TR]
</tbody>[/TABLE]