Sumifs Maybe?

rehberger

Board Regular
Joined
Aug 28, 2013
Messages
52
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.

Acct#DateCost
300510/24/1850
300510/25/18100
30389/1/1825
30389/2/1850
300511/13/1825
300511/14/1850
303810/1/1850
303810/2/1875

<tbody>
</tbody>

Results with formulas in red highlighted cells
Acct#Trip 1Trip 2Trip 3
300515075
303875125

<tbody>
</tbody>

Thanks for any help!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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.

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

Doing that gives me the below result.

Acct#12Grand Total
300515075225
303875125200
Grand Total225200425

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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