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: 10

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top