excellenthelp
New Member
- Joined
- Mar 25, 2016
- Messages
- 2
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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Central[/TD]
[TD]1/31/16[/TD]
[/TR]
[TR]
[TD]Central[/TD]
[TD]1/31/16[/TD]
[/TR]
[TR]
[TD]Central[/TD]
[TD]3/15/16[/TD]
[/TR]
[TR]
[TD]Central[/TD]
[TD]2/1/16[/TD]
[/TR]
[TR]
[TD]Central[/TD]
[TD]3/15/16[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Region[/TD]
[TD]S_Date[/TD]
[TD]E_date[/TD]
[TD]Count (What I'm solving for)[/TD]
[/TR]
[TR]
[TD]Central[/TD]
[TD]1/30/16[/TD]
[TD]3/1/16[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Central[/TD]
[TD]3/2/16[/TD]
[TD]4/2/16[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
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.
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.
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)!
[TABLE="width: 500"]
<tbody>[TR]
[TD]Central[/TD]
[TD]1/31/16[/TD]
[/TR]
[TR]
[TD]Central[/TD]
[TD]1/31/16[/TD]
[/TR]
[TR]
[TD]Central[/TD]
[TD]3/15/16[/TD]
[/TR]
[TR]
[TD]Central[/TD]
[TD]2/1/16[/TD]
[/TR]
[TR]
[TD]Central[/TD]
[TD]3/15/16[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Region[/TD]
[TD]S_Date[/TD]
[TD]E_date[/TD]
[TD]Count (What I'm solving for)[/TD]
[/TR]
[TR]
[TD]Central[/TD]
[TD]1/30/16[/TD]
[TD]3/1/16[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Central[/TD]
[TD]3/2/16[/TD]
[TD]4/2/16[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]
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.
Code:
=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.
Code:
=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)!
