# Sum Distinct Values within Time Constraints?

#### excellerants

##### New Member
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

#### Attachments

• Sample Data.PNG
55.3 KB · Views: 0

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Eric W

##### MrExcel MVP
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
11JazmineUtah125-Apr-2010-Apr-20
12EnriqueOregon161-Apr-204-Apr-20
13EnriqueMontana05-Apr-206-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.

Replies
10
Views
455
Replies
3
Views
112
Replies
2
Views
4K
Replies
6
Views
444