+ Reply to Thread
Results 1 to 7 of 7

Sum values distinct in time

  1. #1
    Registered User
    Join Date
    09-08-2017
    Location
    Lansing, MI
    MS-Off Ver
    MS Office Pro Plus 2013
    Posts
    4

    Sum values distinct in time

    In the attached sheet, I have listed downtime in seconds by station number on my production line. If I look at total downtime for the day by simply totaling up column F, I include values that overlap in time, thereby inflating actual seconds down. For example, the downtime event on row 5 occured within the duration of the downtime event in row 4, therefore I would only want to include the 21 second duration of the event from row 4. Is there a formula or table I could create that would omit these values that occur in the same moments in time and only count distinct values?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,212

    Re: Sum values distinct in time

    Perhaps
    in g2=d2
    in h2 =MAX(ROUND((E2-G2)*24*60*60,0),0)
    in g3 =IF(MAX($E$2:E2)>D3,E2,D3)

    copy h2 and g3 down to the bottom of your data

    Any closer?

  3. #3
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Sum values distinct in time

    main formula
    =SUM(IFERROR((IF(E3>$E$3:$E$2635,$E$3:$E$2635,E3)-IF(D3>$D$3:$D$2635,D3,$D$3:$D$2635))^0.5,)^2)-G3 as array formula
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-08-2017
    Location
    Lansing, MI
    MS-Off Ver
    MS Office Pro Plus 2013
    Posts
    4

    Re: Sum values distinct in time

    Very close, but doesn't seem to work when there are more than 2 overlapping values. Thanks!

  5. #5
    Registered User
    Join Date
    09-08-2017
    Location
    Lansing, MI
    MS-Off Ver
    MS Office Pro Plus 2013
    Posts
    4

    Re: Sum values distinct in time

    Looks like a winner! Thanks!

  6. #6
    Registered User
    Join Date
    09-08-2017
    Location
    Lansing, MI
    MS-Off Ver
    MS Office Pro Plus 2013
    Posts
    4

    Re: Sum values distinct in time

    Actually may have spoken too soon. This method also seems to have an issue when dealing with more than two occurences during the same time period.

  7. #7
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Sum values distinct in time

    =SUM(IFERROR((IF(E3>$E3:$E$2635,$E3:$E$2635,E3)-IF(D3>$D3:$D$2635,D3,$D3:$D$2635))^0.5,)^2)-G3
    and just
    =SUBTOTAL(9,F3:F6000)
    but rslt is the same

+ 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. Deduct amount based on sum of distinct values over a period of time...
    By Mesanic in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-10-2017, 12:11 PM
  2. [SOLVED] get distinct values from criteria AND sum all of the values of the distinct
    By dluhut in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-10-2017, 02:57 PM
  3. Count of Values (Rows) of Most Recent Consecutive Streak (2+ Distinct Values)
    By ExcelForum88 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-13-2016, 12:26 PM
  4. Replies: 0
    Last Post: 07-21-2015, 04:44 PM
  5. Distinct values
    By kushibobby in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-24-2012, 09:15 AM
  6. Listzing distinct values
    By T De Villiers in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-26-2008, 02:46 PM
  7. Distinct Values
    By kittles3069 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-26-2007, 08:41 PM

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