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!
Bookmarks