Sum Distinct Values within Time Constraints?

[excellerants]

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!

Salesperson	State	Potential of State	Begin Date in State	End Date in State
Olivia	Washington	26	April 1, 2020	April 2, 2020
Olivia	California	71	April 3, 2020	April 7, 2020
Olivia	Texas	104	April 8, 2020	April 10, 2020
Carson	Oregon	16	April 1, 2020	April 1, 2020
Carson	Washington	26	April 2, 2020	April 4, 2020
Carson	California	71	April 5, 2020	April 6, 2020
Carson	Utah	12	April 7, 2020	April 9, 2020
Carson	Texas	104	April 10, 2020	April 10, 2020
Jazmine	Nevada	24	April 1, 2020	April 4, 2020
Jazmine	Utah	12	April 5, 2020	April 10, 2020
Enrique	Oregon	16	April 1, 2020	April 4, 2020
Enrique	Montana	0	April 5, 2020	April 6, 2020
Enrique	Nevada	24	April 7, 2020	April 10, 2020

Date	Potential of States	Correct 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

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1
	A	B	C	D	E
1	Salesperson	State	Potential of State	Begin Date in State	End Date in State
2	Olivia	Washington	26	1-Apr-20	2-Apr-20
3	Olivia	California	71	3-Apr-20	7-Apr-20
4	Olivia	Texas	104	8-Apr-20	10-Apr-20
5	Carson	Oregon	16	1-Apr-20	1-Apr-20
6	Carson	Washington	26	2-Apr-20	4-Apr-20
7	Carson	California	71	5-Apr-20	6-Apr-20
8	Carson	Utah	12	7-Apr-20	9-Apr-20
9	Carson	Texas	104	10-Apr-20	10-Apr-20
10	Jazmine	Nevada	24	1-Apr-20	4-Apr-20
11	Jazmine	Utah	12	5-Apr-20	10-Apr-20
12	Enrique	Oregon	16	1-Apr-20	4-Apr-20
13	Enrique	Montana	0	5-Apr-20	6-Apr-20
14	Enrique	Nevada	24	7-Apr-20	10-Apr-20
15
16	Date	Potential of States	Correct Answer Should Be:
17	1-Apr-20	66	66
18	2-Apr-20	66	66
19	3-Apr-20	137	137
20	4-Apr-20	137	137
21	5-Apr-20	83	83
22	6-Apr-20	83	83
23	7-Apr-20	107	107
24	8-Apr-20	140	140
25	9-Apr-20	140	140
26	10-Apr-20	140	140
Sheet11

Cell Formulas
Range	Formula
B17:B26	B17	=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.