Count duplicates within date range

[hinesjoel]

Hi,

I have a dataset that we record interaction with our customers.

Each interaction is logged by Date, customer ID, reason for interaction.

I want to count the number of interactions with the customer from the first interaction for 14 days (days would be changeable based on reporting timeframe needs)

The functions I have been trying count the total interactions with the customer, I am having trouble limiting the count to 14 days.

No VBA if possible.

Any suggestions?

Thanks,

Joel

 

[etaf]

i have just written a solution to a similar question - not quite the same

So would a COUNTIFS() work

this is where a sample would have helped here

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

XL2BB is an add-in used on this forum , see my signature or the menu in post to link to the full details
OR put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

Here is the link to XL2BB instructions

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

Lets Assume the Date is in column A and Customer ID in B , and Interaction in C

=COUNTIFS( $B$2:B2,B2, $A$2:A2, ">="&A2-14 )
should count based on last 14 days

Various.xlsx
	A	B	C
1	Date	Name	Count
2	1/22/22	A	1
3	1/23/22	b	1
4	1/24/22	A	2
5	1/25/22	b	2
6	1/26/22	b	3
7	1/27/22	b	4
8	1/28/22	A	3
9	1/29/22	b	5
10	1/30/22	A	4
11	1/31/22	A	5
12	2/1/22	A	6
13	2/2/22	b	6
14	2/3/22	A	7
15	2/4/22	A	8
16	2/5/22	A	9
17	2/6/22	A	9
18	2/7/22	A	10
19	2/8/22	b	6
20	2/9/22	A	10
21	2/10/22	A	11
Sheet1 (2)

Cell Formulas
Range	Formula
C2:C21	C2	=COUNTIFS( $B$2:B2,B2, $A$2:A2, ">="&A2-14 )

BUT someone here , wanted to reset the date after 14 days , so the count was based not on the last 14 days , but 14 days from the 1st interaction
not sure if my solution actually does work - but have a read here

[ask] Fortnightly rolling count with condition

Hi Excel Gurus, I have one excel sheet containing the extra shift taken by the staff, and I would like to count how many times each staff being assigned to this fortnightly. Each staff can be assigned for up to 3 shift fortnigthy, threrefore I created simple Excel sheet to record these...

www.mrexcel.com

 

[hinesjoel]

Thanks,

The formula worked mostly, but I was unable to have the date range in one argument. I had to add a helper cell with date+14. So I ended up with two criteria. One for => first date and the one =< helper cell +14.

Thanks Again,

 

[Peter_SSs]

What about this?

22 02 28.xlsm
	A	B	C	D	E
1	Date	Name		Name	Count 1st 14 days
2	01-Jan-22	A		A	8
3	02-Jan-22	b		b	6
4	03-Jan-22	A
5	04-Jan-22	b
6	05-Jan-22	b
7	06-Jan-22	b
8	07-Jan-22	A
9	08-Jan-22	b
10	09-Jan-22	A
11	10-Jan-22	A
12	11-Jan-22	A
13	12-Jan-22	b
14	13-Jan-22	A
15	14-Jan-22	A
16	15-Jan-22	A
17	16-Jan-22	A
18	17-Jan-22	A
19	18-Jan-22	b
20	19-Jan-22	A
21	20-Jan-22	A
22
Count contacts

Cell Formulas
Range	Formula
D2:D3	D2	=UNIQUE(B2:B21)
E2:E3	E2	=COUNTIFS(B2:B21,D2#,A2:A21,"<"&MINIFS(A2:A21,B2:B21,D2#)+14)
Dynamic array formulas.