Sumifs Maybe?

rehberger

Board Regular
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# Date Cost 3005 10/24/18 50 3005 10/25/18 100 3038 9/1/18 25 3038 9/2/18 50 3005 11/13/18 25 3005 11/14/18 50 3038 10/1/18 50 3038 10/2/18 75

<tbody>
</tbody>

Results with formulas in red highlighted cells
 Acct# Trip 1 Trip 2 Trip 3 3005 150 75 3038 75 125

<tbody>
</tbody>

Thanks for any help!

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
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# 1 2 Grand Total 3005 150 75 225 3038 75 125 200 Grand Total 225 200 425

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>

Last edited:

Replies
2
Views
45
Replies
1
Views
24
Replies
2
Views
27
Replies
3
Views
348
Replies
4
Views
105