Count Unique Values Only If They Meet Two Criteria

[Gtabtr1]

Hello Good People,

I've been working on a section of code for several days, am not finding any previous posts that are applicable to my situation and am desperately hoping someone is willing to undertake the challenge. I tried posting this problem last week but the responses I received were not applicable so I am trying again. I am not sure if this breaks any rules and I apologize if it does...I'm just, well, like I said, desperate.

I have ID numbers in col A, Dates in col B. The Start Date in cell C1 (1/1/2017)and the end date in Cell D1 (3/31/2017) - the end date is actually dynamic but I know how to address that.

I need to count the number of unique values in column A if the dates in column B are within the range. Here comes the tricky part...I only need to count them if there are 3 or more occurrences. For example: in this data, there is only 1 ID that appears within the date range of 1/1/17 to 3/31/17 three or more times. Although 14 and 503 both appear on the list at least 3 times, the occurrences are not between the date range. ID 414 is the only one with 3 or more occurrences within the stated date range.

My original spreadsheet used the array formula:"=SUM(IF(FREQUENCY(IF(('All Engagement Types'!$B$2:$B$10000>='Weekly BP & Engagement'!$C$1)*('All Engagement Types'!$B$2:$B$10000<='Weekly BP & Engagement'!$A15),MATCH('All Engagement Types'!$A$2:$A$10000,'All Engagement Types'!$A$2:$A$10000,0)),ROW('All Engagement Types'!$B$2:$B$10000)-ROW('All Engagement Types'!$B$2)+1)>=3,1))" but it is more than 255 characters and will not work in VBA.

Any and all suggestions would be greatly appreciated.

[TABLE="width: 135"]
<tbody>[TR]
[TD="align: center"]COL A
ID[/TD]
[TD="align: center"]COL B
Due Date[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]03/07/2017[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]01/31/2017[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]06/17/2016[/TD]
[/TR]
[TR]
[TD="align: center"]414[/TD]
[TD="align: center"]03/16/2017[/TD]
[/TR]
[TR]
[TD="align: center"]414[/TD]
[TD="align: center"]02/15/2017[/TD]
[/TR]
[TR]
[TD="align: center"]414[/TD]
[TD="align: center"]01/31/2017[/TD]
[/TR]
[TR]
[TD="align: center"]503[/TD]
[TD="align: center"]03/14/2017[/TD]
[/TR]
[TR]
[TD="align: center"]503[/TD]
[TD="align: center"]02/14/2017[/TD]
[/TR]
[TR]
[TD="align: center"]503[/TD]
[TD="align: center"]12/12/2016[/TD]
[/TR]
[TR]
[TD="align: center"]503[/TD]
[TD="align: center"]10/10/2016[/TD]
[/TR]
[TR]
[TD="align: center"]503[/TD]
[TD="align: center"]09/16/2016[/TD]
[/TR]
[TR]
[TD="align: center"]503[/TD]
[TD="align: center"]09/15/2016[/TD]
[/TR]
[TR]
[TD="align: center"]503[/TD]
[TD="align: center"]08/29/2016[/TD]
[/TR]
[TR]
[TD="align: center"]503[/TD]
[TD="align: center"]07/14/2016[/TD]
[/TR]
[TR]
[TD="align: center"]503[/TD]
[TD="align: center"]06/29/2016[/TD]
[/TR]
[TR]
[TD="align: center"]503[/TD]
[TD="align: center"]06/16/2016[/TD]
[/TR]
[TR]
[TD="align: center"]503[/TD]
[TD="align: center"]06/09/2016[/TD]
[/TR]
[TR]
[TD="align: center"]503[/TD]
[TD="align: center"]05/23/2016[/TD]
[/TR]
</tbody><colgroup><col style="text-align: center;"><col style="text-align: center;"></colgroup>[/TABLE]

 

[robrados]

Hi Gtabtr

try the following array formula:
=IF(A2=A1,"",IF(SUM(1*((A2=$A$2:$A$17)*(($B$2:$B$17<=$F$2)*($B$2:$B$17>=$E$2))))>=3,SUM($C$1:C1)+1,""))

paste it in the cell right next to your first date, the cell above has to be empty.

Look at the screenshot for clarification.

 

[robrados]

here is the image
Imgur: The most awesome images on the Internet

 

[robrados]

sorry there was a little mistake in my formula, at the end you need to use the count formula and not the sum formula...

=IF(A22=A21,"",IF(SUM(1*((A22=$A$2:$A$23)*(($B$2:$B$23<=$F$2)*($B$2:$B$23>=$E$2))))>=3,COUNT($C$1:C21)+1,""))

why cant I edit my posts??

 

[Gtabtr1]

Hello robrados and thank you! My firm will not allow me to access the images you posted so I apologize for the questions. I put the array formula in C2, filled in E2 with "1/1/2017" and F2 with "3/31/2017" but the result in C2 was "1" which is incorrect. Did I miss something? Should there be a value in C1? Should I copy it all the way down column C?

Thank you again.

 

[robrados]

hello Gtabtr

yes you have to copy the formula untill the end of your data set.
it works like an ongoing counter and the last number in the column C will be the sum of total occurences which match your criteria.

greetings

 

[Martvg]

Hi,

This might help you out:
=SUM(IF(FREQUENCY(IF((B2:B19<D8)*(B2:B19>D7),A2:A19),IF((B2:B19<D8)*(B2:B19>D7),A2:A19))>=3,1))
As an Array formula.

A bit strange that you just mentioned that the result for 1/1/2017 to 3/31/2017 shouldn't be 1. In your first post you specified that it should:

I need to count the number of unique values in column A if the dates in column B are within the range. Here comes the tricky part...I only need to count them if there are 3 or more occurrences. For example: in this data, there is only 1 ID that appears within the date range of 1/1/17 to 3/31/17 three or more times. Although 14 and 503 both appear on the list at least 3 times, the occurrences are not between the date range. ID 414 is the only one with 3 or more occurrences within the stated date range.

Could you explain this any further?

Best regards,

 

[Aladin Akyurek]

control+shift+enter, not just enter:

=sum(if(frequency(if(b2:b19 >= c1,if(b2:b19 <= d1,a2:a19)),a2:a19) >= 3,1))

 

[Gtabtr1]

Hello Martvg,
The original data set I posted was a fraction of the real data. I have almost 20,000 rows to evaluate every week which is why I am so thankful for the assistance.

I know array formulas must use ctrl+shift+enter. Another complication I did not include is that the result is on a different sheet than the data which means the array formulas surpass 255 characters once I add all the sheet and cell references. I am really looking for code rather than a formula but will try again with these new formulas. Thank you all!