# Substract values based on date criteria

1. ## 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

2. ## Re: Substract values based on date criteria

Attach a sample workbook, showing what you want as the expected results for that data.

4. ## Re: Substract values based on date criteria

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.

5. ## Re: Substract values based on date criteria

Hi, thank you for this...I see the light on the end of a tunnel. I have attached new sheet with more data but it does not calculate coreectly - you will see.

6. ## Re: Substract values based on date criteria

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

7. ## Re: Substract values based on date criteria

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.

8. ## Re: Substract values based on date criteria

Hi, this is the result in green what I would like to have. The importante thing is that difference between kilometers is calculated only with the rows that have same licence plate numbers.

9. ## Re: Substract values based on date criteria

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.``

10. ## Re: Substract values based on date criteria

Thank you very much for this - it is just like a Bethoveen 9th symphony

11. ## Re: Substract values based on date criteria

If problem solved pl mark the thread solved.

12. ## Re: Substract values based on date criteria

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

13. ## Re: Substract values based on date criteria

Sorry for that - it is solved. THanks again!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1