Calculate # repurchases within 30 days of last sale

[M10Ant]

Hoping someone might be able to help as currently at a loss on working out a formula for this

I need a formula that will count the number of times a stock has been bought within 30 days of the last sale of that stock.

In the below example, my summary table on the right should be recording a count of "2" for APPL stock and "1" for MSFT stock.

For the APPL stock, the count of 2 would come from the two Buy orders on 16/06/2021 and 22/07/2021, both being within 30 days of the last sale of APPL stock on 10/07/2021.
For the MFST stock, the count of 1 would come from the Buy order on 26/07/2021, being within 30 days of the last sale of MFST stock on 23/07/2021

No of repurchases within 30 days of last disposal.xlsx
	A	B	C	D	E	F	G
1
2		Trade Execution Time	Order Type	Stock		Stock	No. of Repurchases within 30 days of last sale
3		03/06/2021	Buy	APPL		APPL
4		07/06/2021	Buy	APPL		MSFT
5		14/06/2021	Buy	MSFT
6		15/06/2021	Buy	APPL
7		22/06/2021	Buy	MSFT
8		10/07/2021	Sell	APPL
9		16/07/2021	Buy	APPL
10		22/07/2021	Buy	APPL
11		23/07/2021	Sell	MSFT
12		26/07/2021	Buy	MSFT
13		05/11/2021	Buy	MSFT
Sheet1

I think I might need to use COUNTIFS or MAX, but so far have only worked out how to count the number of trades for each different type of stock, not a count of all buy orders within 30 days of the last sale (per each type of stock).
I'm also running Excel 365 so have access to the new MINIFS, MAXIFS functions. I just can't work out how to solve this currently.


Thanks in advance.

 

[M10Ant]

A picture probably explains it a lot better! Below is what I'm trying to achieve:

 

[Peter_SSs]

Sorry I appreciate I may not have explained the problem fully initially.
I'm needing a formula that records the number of buys of the same stock that has occurred within 30 days following a sell order for that stock prior, and for any time this has occurred.

Yes, that is a very different requirement to the initial explanation!

I would add another column to the first table. You can hide the column if you want.

M10Ant_1.xlsm
	A	B	C	D	E	F	G	H
1
2		Trade Execution Time	Order Type	Stock	Rebuy within 30 days		Stock	No. of Repurchases within 30 days of last sale
3		3/06/2021	Buy	APPL			APPL	2
4		7/06/2021	Buy	APPL			MSFT	2
5		14/06/2021	Buy	MSFT
6		15/06/2021	Buy	APPL
7		22/06/2021	Buy	MSFT
8		10/07/2021	Sell	APPL
9		16/07/2021	Buy	APPL	1
10		22/07/2021	Buy	APPL	1
11		23/07/2021	Sell	MSFT
12		26/07/2021	Buy	MSFT	1
13		5/11/2021	Buy	MSFT
14		6/12/2021	Sell	MSFT
15		10/12/2021	Buy	MSFT	1
16
Bought

Cell Formulas
Range	Formula
H3:H4	H3	=SUMIF(Table1[Stock],[@Stock],Table1[Rebuy within 30 days])
E3:E15	E3	=LET(tet,[Trade Execution Time],ctet,[@[Trade Execution Time]],IF([@[Order Type]]="Buy",IF(ctet-IFERROR(AGGREGATE(14,6,tet/((tet<ctet)*([Stock]=[@Stock])*([Order Type]="Sell")),1),0)<=30,1,""),""))

 

[M10Ant]

Thank you very much Peter I've tested on my end and it's exactly what I need.

 

[Peter_SSs]

Glad we got there in the end.
Thanks for the confirmation.