+ Reply to Thread
Results 1 to 13 of 13

Substract values based on date criteria

  1. #1
    Registered User
    Join Date
    08-09-2018
    Location
    Zagreb, Croatia
    MS-Off Ver
    MS Office 365 Business
    Posts
    6

    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
    Attached Files Attached Files
    Last edited by gvidic; 08-09-2018 at 03:21 AM.

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Substract values based on date criteria

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

    Go Advanced -> Manage Attachments -> Upload

  3. #3
    Registered User
    Join Date
    08-09-2018
    Location
    Zagreb, Croatia
    MS-Off Ver
    MS Office 365 Business
    Posts
    6

    Re: Substract values based on date criteria

    I did - thanks ahead.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,052

    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: copy to clipboard
    Please Login or Register  to view this content.

    Column G is populated using:
    Formula: copy to clipboard
    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    08-09-2018
    Location
    Zagreb, Croatia
    MS-Off Ver
    MS Office 365 Business
    Posts
    6

    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.
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,469

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


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,081

    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.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  8. #8
    Registered User
    Join Date
    08-09-2018
    Location
    Zagreb, Croatia
    MS-Off Ver
    MS Office 365 Business
    Posts
    6

    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.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,081

    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.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-09-2018
    Location
    Zagreb, Croatia
    MS-Off Ver
    MS Office 365 Business
    Posts
    6

    Re: Substract values based on date criteria

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

  11. #11
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,081

    Re: Substract values based on date criteria

    If problem solved pl mark the thread solved.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,469

    Re: Substract values based on date criteria

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

  13. #13
    Registered User
    Join Date
    08-09-2018
    Location
    Zagreb, Croatia
    MS-Off Ver
    MS Office 365 Business
    Posts
    6

    Re: Substract values based on date criteria

    Sorry for that - it is solved. THanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Return amount values based on date and name criteria
    By MyStix01 in forum Excel General
    Replies: 2
    Last Post: 10-09-2017, 09:26 PM
  2. Sum of unique values with mulitple criteria based on date :/
    By andylee1892 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-18-2017, 11:11 PM
  3. Formula to return values based on criteria and date range
    By jr217 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-28-2014, 06:20 AM
  4. [SOLVED] difference of values based on date and criteria
    By darkhangelsk in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-28-2013, 03:06 PM
  5. [SOLVED] Would like to count cell values based on date criteria in a different range
    By sdavison in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-23-2013, 12:45 AM
  6. Remove duplicate values based on a date criteria
    By nomis6565 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-20-2013, 01:47 AM
  7. Macro to convert formuales into values based on date criteria
    By ash_farooq in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-29-2013, 01:38 AM

Bookmarks

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