Count specific unique values based on multiple criteria within a variable date range

[KennedyA12]

Okay..so I'm stumped. To simplify... my worksheet looks like this

A: B: C:
ID | Month/Date |Unique Count

1121|01/2015 |
1121|01/2015 |
1131|01/2015 |
1141|02/2015 |
1121|02/2015 |
1151|02/2015 |
1121|03/2015 |
1141|03/2015 |
1121|04/2015 |
1131|04/2015 |


What I'm trying to do is count the number of unique instances that an ID occurs within a specific month/date range.

For example.. ID # "1121" above, I want to say in the two months before or the two months after the specific cells Month/Date, how many times has that ID appeared. So in the example above for A2: ID 1121 has appeared 3 unique times between January - March of 2015. The issue is the data range # of rows that account for each data range are variable. Each month could be thousands of more or fewer records than the previous month.

I want to identify how many times each ID has occurred within a +-3 month range ignoring multiple IDs within the same month.

So I've got this...which works in that it counts an ID unique only if the ID and month/year are different.

=SUM(IF(FREQUENCY(IF($A$2:$A$10=A2,IF($B$2:$B$10<>"",MATCH("~"&$B$2:$B$10,$B$2:$B$10&"",0))),ROW($B$2:$B$10)-ROW(B2)+1),1))

Only issue is defining the range...I'd want it to be only counting instances within the cell's current month +- 3
months.

Hopefully that makes sense...I don't even know if something that convoluted is possible.

Thank you for your thoughts!

 

[Chrisdontm]

I have an answer for you, but I had to add two helper columns to do it...
At least it will give you what you want...
You can access it through a " DropBox " link;

https://www.dropbox.com/s/twkt7qz8cqj0mxn/KennedyA12-helperpage.xlsx?dl=0

 

[Marcelo Branco]

Maybe something like this

	A​	B​	C​	D​	E​	F​
1​	ID​	Month/Date​	ID​	First Month​	Last Month​	Unique Count​
2​	1121​	jan/15​	1121​	jan/15​	mar/15​	3​
3​	1121​	jan/15​
4​	1131​	jan/15​
5​	1141​	fev/15​
6​	1121​	fev/15​
7​	1151​	fev/15​
8​	1121​	mar/15​
9​	1141​	mar/15​
10​	1121​	abr/15​
11​	1131​	abr/15​

<tbody>
</tbody>

Search parameters in C2:E2

Array formula in F2
=SUM(IF(FREQUENCY(IF($A$2:$A$100=C2,IF($B$2:$B$100>=D2,IF($B$2:$B$100<=EOMONTH(E2,0),MATCH("~"&$B$2:$B$100,$B$2:$B$100&"",0)))),ROW($B$2:$B$100)-ROW($B$2)+1),1))

confirmed with Ctrl+Shift+Enter

Hope this helps

M.