KennedyA12
New Member
- Joined
- Feb 10, 2015
- Messages
- 8
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!
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!
