Counting duplicate within 90 days

[Jzaid]

I need to count if a unique id has been used within the previous 90 days of the most recent used date. It is considered a duplicate

if it has been used within the previous 90 days, however it if was used greater than 90 days it is not a duplicate. I need this to run off the date the unique Id was used, and not off of today’s date.

Column A is date
Column B is unique id
Column C would then be the formula.

 

[maabadi]

Welcome to MrExcel Message Board.
Try this At C2 & Drag it Down

=SUMPRODUCT(($B$2:$B$13=B2)*((TODAY()-$A$2:$A$13)<=90))

 

[Jzaid]

My initial trial with this didn’t work, I tried changing the absolutely reference $b$13/$a$13, which did ensure the forumla was only referencing in the past. One complication I should have explained, there may be hundreds of entries per date of different unique ids, not a single entry per date.

 

[maabadi]

Please upload your example file & Desired Results with XL2BB ADDIN (Preferable) OR upload it at free uploading site e.g. www.dropbox.com or googledrive or onedrive and insert link here.

 

[Jzaid]

Example Sheet

Sheet1 Collection Date,Result,Unique ID,Ideal Results 3/6/2020,NOT DETECTED,394128,I need to know if COUNTIFS(c:c,c2,B:B, b2) as occured within the last 90 days. I can get it to work within the last 90 rows, but am struggling with adding the date measure, as i can have so many entries per date. ...

docs.google.com

here is a link for an example.

 

[Peter_SSs]

here is a link for an example.

The link comes up with an "Access Denied" tab stating "You need access". XL2BB as suggested by maabadi will allow you to post a small sample directly into a post here.

Also, is this a Google Sheets question or an Excel question?

 

[Jzaid]

Apologies I’ve turned on link sharing. This is an excel question, I cannot download xl2bb add on to my work computer.

 

[maabadi]

Your date inputed as Text then Countif doesn't work. you have two ways:
1. At column E write =B2*1 & drag it down then replaced Column A date with column E with Paste as Values.Now use this Formula:

=COUNTIFS(C:C,C2,B:B, B2,A:A, ">=" & TODAY()-90)

OR
2. SUMPRODUCT

=SUMPRODUCT(($B:$B=B2)*($C:$C=C2)*($A:$A<=(TODAY()-90)))

 

[Jzaid]

Your date inputed as Text then Countif doesn't work. you have two ways:
1. At column E write =B2*1 & drag it down then replaced Column A date with column E with Paste as Values.Now use this Formula:

=COUNTIFS(C:C,C2,B:B, B2,A:A, ">=" & TODAY()-90)

OR
2. SUMPRODUCT

=SUMPRODUCT(($B:$B=B2)*($C:$C=C2)*($A:$A<=(TODAY()-90)))

Thanks for the help!

 

[maabadi]

You're Welcome & Thanks for Feedback.