How to obtain average values on columns filter

[pankajgrover]

Here below example data : where results are coming in 3 columns: Particluar, Qty , Average
I want to formula check D8 Col. BUY/SELL filter, and displayed results like Particluar, Buy Qty , Buy Average, Sell Qty , Sell Average,
How to edit my formulas to displayed this results : Particluar, Buy Qty , Buy Average, Sell Qty , Sell Average

Shares Search Filter APPLE.xlsx
	A	B	C	D	E	F	G		I	J		L
1		Advanced Search Bar - All Column Search
2
3
4
5									Unique Stocks Average
6			189	Average	2013.07	380470.60
7	Date	Particular	Qty	Buy/Sell	Price	Totals			Particular	Qty		AVG
8	06/08/2024	MICROSOFT	25	BUY	135.70	3392.50	1		MICROSOFT	66		4346.09
9	06/08/2024	SAMSUNG	5	BUY	643.50	3217.50	1		SAMSUNG	10		1430.08
10	06/08/2024	NETFLIX	11	BUY	3347.70	36824.70	1		NETFLIX	36		1578.67
11	06/08/2024	APPLE	50	BUY	287.00	14350.00	1		APPLE	77		292.16
12	06/08/2024	MICROSOFT	5	SELL	49899.90	249499.50	1
13	06/08/2024	APPLE	25	BUY	287.80	7195.00	1
14	06/08/2024	MICROSOFT	11	BUY	1610.65	17717.15	1
15	06/08/2024	SAMSUNG	4	BUY	2651.60	10606.40	1
16	05/08/2024	APPLE	1	SELL	475.50	475.50	1
17	05/08/2024	MICROSOFT	25	BUY	649.30	16232.50	1
18	05/08/2024	SAMSUNG	1	BUY	476.85	476.85	1
19	04/08/2024	NETFLIX	25	BUY	800.30	20007.50	1
20	04/08/2024	APPLE	1	SELL	475.50	475.50	1
search_bar

Cell Formulas
Range	Formula
C6,F6	C6	=SUBTOTAL(9,C8:C1000)
E6	E6	=F6/C6
A8:F20	A8	=FILTER(my_data,NOT(ISERROR(SEARCH(A1,my_data[Particular]))),"")
I8:I11	I8	=FILTER(UNIQUE($B$8:$B$1000),UNIQUE($B$8:$B$1000)<>0)
J8	J8	=SUMIFS($C$8:$C$1000,$B$8:$B$1000,I8,$G$8:$G$1000,1)
L8:L20	L8	=IFERROR(K8/J8, "")
J9:J20	J9	=SUMIFS($C$8:$C$1000,$B$8:$B$1000,I9)
G8:G20	G8	=SUBTOTAL(103,A8)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A8:F1000	Cell	does not contain a blank value	text	NO

 

[StephenCrump]

Like this perhaps?

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Advanced Search Bar - All Column Search
2
3
4
5									Unique Stocks Average
6			189	Average	2,013.07	380,470.60				Buy	Buy	Sell	Sell
7	Date	Particular	Qty	Buy/Sell	Price	Totals			Particular	Qty	AVG	Qty	AVG
8	6 Aug 2024	MICROSOFT	25	BUY	135.70	3,392.50			MICROSOFT	61	612.17	5	49,899.90
9	6 Aug 2024	SAMSUNG	5	BUY	643.50	3,217.50			SAMSUNG	10	1,430.08	0	-
10	6 Aug 2024	NETFLIX	11	BUY	3,347.70	36,824.70			NETFLIX	36	1,578.67	0	-
11	6 Aug 2024	APPLE	50	BUY	287.00	14,350.00			APPLE	75	287.27	2	475.5
12	6 Aug 2024	MICROSOFT	5	SELL	49,899.90	249,499.50
13	6 Aug 2024	APPLE	25	BUY	287.80	7,195.00
14	6 Aug 2024	MICROSOFT	11	BUY	1,610.65	17,717.15
15	6 Aug 2024	SAMSUNG	4	BUY	2,651.60	10,606.40
16	5 Aug 2024	APPLE	1	SELL	475.50	475.50
17	5 Aug 2024	MICROSOFT	25	BUY	649.30	16,232.50
18	5 Aug 2024	SAMSUNG	1	BUY	476.85	476.85
19	4 Aug 2024	NETFLIX	25	BUY	800.30	20,007.50
20	4 Aug 2024	APPLE	1	SELL	475.50	475.50
21
Sheet1

Cell Formulas
Range	Formula
C6,F6	C6	=SUBTOTAL(9,C8:C20)
E6	E6	=F6/C6
I8:I11	I8	=LET(u,UNIQUE($B$8:$B$20),FILTER(u,u<>0))
J8:J11,L8:L11	J8	=BYROW($I8#,LAMBDA(r,SUMIFS($C$8:$C$20,$D$8:$D$20,J$6,$B$8:$B$20,r)))
K8:K11,M8:M11	K8	=IFERROR(BYROW($I8#,LAMBDA(r,SUMIFS($F$8:$F$20,$D$8:$D$20,K$6,$B$8:$B$20,r)))/J8#,"-")
Dynamic array formulas.

 

[alansidman]

With power query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Particular", type text}, {"Qty", Int64.Type}, {"Buy/Sell", type text}, {"Price", type number}, {"Totals", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Particular", "Buy/Sell"}, {{"Total", each List.Sum([Qty]), type nullable number}, {"Average", each List.Average([Price]), type nullable number}})
in
    #"Grouped Rows"

Book6
	A	B	C	D	E	F	G	H	I	J	K
2	Date	Particular	Qty	Buy/Sell	Price	Totals		Particular	Buy/Sell	Total	Average
3	45510	MICROSOFT	25	BUY	135.7	3392.5		MICROSOFT	BUY	61	798.55
4	45510	SAMSUNG	5	BUY	643.5	3217.5		SAMSUNG	BUY	10	1257.316667
5	45510	NETFLIX	11	BUY	3347.7	36824.7		NETFLIX	BUY	36	2074
6	45510	APPLE	50	BUY	287	14350		APPLE	BUY	75	287.4
7	45510	MICROSOFT	5	SELL	49899.9	249499.5		MICROSOFT	SELL	5	49899.9
8	45510	APPLE	25	BUY	287.8	7195		APPLE	SELL	2	475.5
9	45510	MICROSOFT	11	BUY	1610.65	17717.15
10	45510	SAMSUNG	4	BUY	2651.6	10606.4
11	45509	APPLE	1	SELL	475.5	475.5
12	45509	MICROSOFT	25	BUY	649.3	16232.5
13	45509	SAMSUNG	1	BUY	476.85	476.85
14	45508	NETFLIX	25	BUY	800.3	20007.5
15	45508	APPLE	1	SELL	475.5	475.5
Sheet1

 

[StephenCrump]

Same question here, already answered for similar variations: How to filter column with same unique match name and to obtain their average values