Substract values based on date criteria

Hi there,

I am trying to get a formula to substract a value based on several criteria:

As you can see below, this is table from gas Company that sends data in Excel regarding truck gas fill ups. In Km column, I would like to have a formula which will calculate difference in kilometers between each gas fill up. For example the truck lic. plate MB GV 403, the kilometer difference between two fill ups (2.8.2018, and previous fill up on 27.7.2018) is 2660 km. Trucks can have one, two, or three tanks, so that is why you have two gas fill up on the the same date with same kilometer amount. Also, I would like formula to only calculate Diesel in Gas column, not Urea. I hope that I clarified it enough for you to understand my goal and hope you could provide me with the solutions.


I tried sumifs formula that was working only if there was one tank truck, but sum range will sum every row with the same date....

Table is attached for better viewing.

Table.png

Hello gvidic and Welcome to Excel Forum.
This proposed solution employs a helper column which may be moved and/or hidden for aesthetic purposes.
The helper column (H) is populated using:

Formula:

Please Login or Register  to view this content.

Column G is populated using:

Formula:

Please Login or Register  to view this content.

Note that this proposal yields the same values as those that were manually placed in column G, however there are no instances of multiple fill ups on the same date. To attempt to find a formula or code solution for multiple fill ups on the same date please upload another file demonstrating expected results under those conditions.
Let us know if you have any questions.

Please add another column and manually enter the results you are expecting for the first 20-30 rows.

Try this.
ARRAY formula in F2 then drag down

Please Login or Register  to view this content.

How ARRAY formula is entered

Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.

Formula is working. Pl see file. Anyway I have slightly modified to avoid 0 in the cell.ARRAY formula is used.

Please Login or Register  to view this content.

If problem solved pl mark the thread solved.

I've marked the thread as solved, since the OP seems to have left.