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!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
736
Office Version
  1. 365
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,108,655
Messages
5,524,124
Members
409,561
Latest member
ay123

This Week's Hot Topics

Top