+ Reply to Thread
Results 1 to 7 of 7

Sum Distinct Values within Time Constraints?

  1. #1
    Registered User
    Join Date
    10-21-2020
    Location
    New York City
    MS-Off Ver
    2016
    Posts
    2

    Sum Distinct Values within Time Constraints?

    Hi All,

    I'm trying to solve the following problem using some combination of sumproduct, sumif, and countif functions: I have a dataset where people move into and out of areas at different times. Sometimes there are multiple people in an area at a time, other times there are none. Each area has a value associated with it.

    I am trying to calculate the the sum of the values of each distinct area at successive time intervals if that area had at least one person in it at that time. I have attached a brief fictitious example and have manually calculated the correct result next to the cells with red question marks.

    My thinking so far has been to use a technique combining SUMPRODUCT with 1/COUNTIF to control for duplicate values, a technique documented on ExcelJet article Count unique values in a range with COUNTIF. However, I haven't been able to successfully apply the time constraints to this formula.

    Any help is immensely appreciated!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    869

    Re: Sum Distinct Values within Time Constraints?

    B20=IF($A20<>"",SUM(IF(FREQUENCY(IF($D$3:$D$15<=$A20,IF($E$3:$E$15>=$A20,MATCH($B$3:$B$15&$C$3:$C$15,$B$3:$B$15&$C$3:$C$15,0))),ROW($B$3:$B$15)-ROW($B$3)+1),$C$3:$C$15)),"")

    Control+shift+enter

    copy down

  3. #3
    Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    293

    Re: Sum Distinct Values within Time Constraints?

    Hi - can you perhaps explain in more details what exactly you are looking for or what you mean by each distinct area at a successive time interval?
    I don't see any pattern with the formulas that you have in place with what you are looking for/

  4. #4
    Forum Contributor
    Join Date
    08-26-2016
    Location
    UK
    MS-Off Ver
    O365
    Posts
    234

    Re: Sum Distinct Values within Time Constraints?

    Either I've understood wrong or your 'should-be' results are awry.
    Below, the green table in the bottom right is my calculations. Compared to yours, only 2 dates agree, the 3rd & 4th April.
    I've taken 10th April as a sample date and highlighted the rows in the top table which include the 10th April and put the corresponding state potentials in column F and totalled them in cell F16 (244) which tallies with the value in the green table for 10th April.
    If I'm wrong, can you explain a bit more how your figures are arrived at?
    Attached Images Attached Images
    Last edited by p45cal; 10-21-2020 at 07:53 PM.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,199

    Re: Sum Distinct Values within Time Constraints?

    Aggree with CARACALLA in #2, same result:
    Please Login or Register  to view this content.
    Array formula should me confirmed with Ctrl-shift-enter combination.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    08-26-2016
    Location
    UK
    MS-Off Ver
    O365
    Posts
    234

    Re: Sum Distinct Values within Time Constraints?

    Quote Originally Posted by p45cal View Post
    Either I've understood wrong or
    …and I've understood wrong; should've read the question more carefully.
    Attached is a Power Query solution at cell D19.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-21-2020
    Location
    New York City
    MS-Off Ver
    2016
    Posts
    2

    Re: Sum Distinct Values within Time Constraints?

    Hi p45cal - thank you for taking the time to post (as well as to follow up on your own post!). My preferred approach here is formulaic given how the reports I am using are configured. However, if some of the other formulaic solutions proposed on this thread ultimately have performance issues due to the volume of data I must analyze, this PowerQuery approach looks to also be a viable solution. I will also keep this in my back pocket if I eventually decide to employ PowerBI to analyze these datasets.

    Cheers!

+ 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. Sum values distinct in time
    By jeremy62375 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-08-2017, 11:38 AM
  2. 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
  3. [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
  4. [SOLVED] Max function with time constraints
    By invendis in forum Excel General
    Replies: 6
    Last Post: 11-15-2016, 06:39 AM
  5. Time Constraints
    By atrades in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-06-2013, 02:19 PM
  6. time constraints for SLA
    By penfold1992 in forum Excel General
    Replies: 14
    Last Post: 10-29-2012, 06:35 AM
  7. Standard Deviation Function with Time Constraints
    By ckatzman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-10-2012, 06:36 PM

Tags for this Thread

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