Counting distinct combo of multiple values within date range

[excellenthelp]

I've been stuck on this for a while now and would great appreciate any input you may have on this. I have a table with two values, Region and 'Start Date'. What I would like to do is get a count of all distinct combinations within a specified date range. I have 2 tables below, one to show an example of my data and another to show what I'm expecting from my output table.

Central	1/31/16
Central	1/31/16
Central	3/15/16
Central	2/1/16
Central	3/15/16

<tbody>
</tbody>

Region	S_Date	E_date	Count (What I'm solving for)
Central	1/30/16	3/1/16	2
Central	3/2/16	4/2/16	1

<tbody>
</tbody>

I have already have a formula for counting all occurrences where column A has 'Central' and the date in column B falls within a date range, but am having difficulty adapting this to only include exact matches once.

=COUNTIFS('Active + Archive'!S:S,'Data Summaries'!A12,'Active + Archive'!V:V,">="&B52,'Active + Archive'!V:V,"<="&D52)

S:S = my column for region denoting the criteria range to check
A12 = The value being checked in the range, in this case it's Central
V:V = The start date, date that I want to ensure is within the range
B52 = First date in the range I want to check
D52 = Last date in the range I want to check

I'm not sure if I need frequency, sumproduct, arrays, or what. From reading other posts I cobbled the below code together, but that's giving me decimals which can't be correct.

=SUMPRODUCT(IF(('Active + Archive'!V:V<=D52)*('Active + Archive'!V:V>=B52), 1/COUNTIFS('Active + Archive'!V:V, "<="&D52, 'Active + Archive'!V:V, ">="&D52, 'Active + Archive'!S:S, A52), 0))

Any insight you might be able to provide would be extremely appreciated, I'm suspecting that whatever I'm missing should be a quick fix (or at least that's what I'm hoping)!

 

[Eric W]

See if this works for you:

	A	B	C	D
1	Central	1/31/2016
2	Central	1/31/2016
3	Central	3/15/2016
4	Central	2/1/2016
5	Central	3/15/2016
6
7
8
9	Region	S_Date	E_date	Count
10	Central	1/30/2016	3/1/2016	2
11	Central	3/2/2016	4/2/2016	1
12

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula D10 {=SUM(IF(FREQUENCY(IF(($B$1:$B$5>=$B10)*($B$1:$B$5<=$C10)*($A$1:$A$5=$A10),$B$1:$B$5),$B$1:$B$5),1))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[excellenthelp]

See if this works for you:

	A	B	C	D
1	Central	1/31/2016
2	Central	1/31/2016
3	Central	3/15/2016
4	Central	2/1/2016
5	Central	3/15/2016
6
7
8
9	Region	S_Date	E_date	Count
10	Central	1/30/2016	3/1/2016	2
11	Central	3/2/2016	4/2/2016	1
12

<tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula D10 {=SUM(IF(FREQUENCY(IF(($B$1:$B$5>=$B10)*($B$1:$B$5<=$C10)*($A$1:$A$5=$A10),$B$1:$B$5),$B$1:$B$5),1))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Just ran through some testing and that did the trick, thanks so much for your help!

 

[Eric W]

Glad to help! Thanks for the feedback.