Sum Distinct Values within Time Constraints?

excellerants

New Member
Joined
Oct 21, 2020
Messages
1
Office Version
  1. 2013
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 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!



SalespersonStatePotential of StateBegin Date in StateEnd Date in State
OliviaWashington26April 1, 2020April 2, 2020
OliviaCalifornia71April 3, 2020April 7, 2020
OliviaTexas104April 8, 2020April 10, 2020
CarsonOregon16April 1, 2020April 1, 2020
CarsonWashington26April 2, 2020April 4, 2020
CarsonCalifornia71April 5, 2020April 6, 2020
CarsonUtah12April 7, 2020April 9, 2020
CarsonTexas104April 10, 2020April 10, 2020
JazmineNevada24April 1, 2020April 4, 2020
JazmineUtah12April 5, 2020April 10, 2020
EnriqueOregon16April 1, 2020April 4, 2020
EnriqueMontana0April 5, 2020April 6, 2020
EnriqueNevada24April 7, 2020April 10, 2020

DatePotential of StatesCorrect Answer Should Be:
April 1, 2020?66
April 2, 2020?66
April 3, 2020?137
April 4, 2020?137
April 5, 2020?83
April 6, 2020?83
April 7, 2020?107
April 8, 2020?140
April 9, 2020?140
April 10, 2020?140
 

Attachments

  • Sample Data.PNG
    Sample Data.PNG
    55.3 KB · Views: 0

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,486
Welcome to the MrExcel forum!

Try:

Book1
ABCDE
1SalespersonStatePotential of StateBegin Date in StateEnd Date in State
2OliviaWashington261-Apr-202-Apr-20
3OliviaCalifornia713-Apr-207-Apr-20
4OliviaTexas1048-Apr-2010-Apr-20
5CarsonOregon161-Apr-201-Apr-20
6CarsonWashington262-Apr-204-Apr-20
7CarsonCalifornia715-Apr-206-Apr-20
8CarsonUtah127-Apr-209-Apr-20
9CarsonTexas10410-Apr-2010-Apr-20
10JazmineNevada241-Apr-204-Apr-20
11JazmineUtah125-Apr-2010-Apr-20
12EnriqueOregon161-Apr-204-Apr-20
13EnriqueMontana05-Apr-206-Apr-20
14EnriqueNevada247-Apr-2010-Apr-20
15
16DatePotential of StatesCorrect Answer Should Be:
171-Apr-206666
182-Apr-206666
193-Apr-20137137
204-Apr-20137137
215-Apr-208383
226-Apr-208383
237-Apr-20107107
248-Apr-20140140
259-Apr-20140140
2610-Apr-20140140
Sheet11
Cell Formulas
RangeFormula
B17:B26B17=SUM(IFERROR(SUMIFS($C$2:$C$14,$B$2:$B$14,IF(MATCH($B$2:$B$14,$B$2:$B$14,0)=ROW($B$2:$B$14)-ROW($B$2)+1,$B$2:$B$14,"xx"),$D$2:$D$14,"<="&A17,$E$2:$E$14,">="&A17)/COUNTIFS($B$2:$B$14,$B$2:$B$14,$D$2:$D$14,"<="&A17,$E$2:$E$14,">="&A17),0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,773
Messages
5,574,155
Members
412,574
Latest member
shadowfighter666
Top