SUMIFS function not filter values as i filter data from dropdown arrow

[pankajgrover]

Shares Search Filter 2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	APPLE	Advanced Search Bar - All Column Search
2
3
4
5
6			76	Average	289.74	22020.50
7	Date	Particular	Qty	Buy/Sell	Price	Totals			Particular	Qty	Totals	AVG
8	06/08/2024	APPLE	50	BUY	287.00	14350.00			APPLE	81	54702.25	675.34
9	06/08/2024	APPLE	25	BUY	287.80	7195.00					0.00
10	06/08/2024	APPLE	1	SELL	475.50	475.50					0.00
search_bar

Cell Formulas
Range	Formula
C6,F6	C6	=SUBTOTAL(9,C8:C1000)
E6	E6	=F6/C6
A8:F11	A8	=FILTER(my_data,NOT(ISERROR(SEARCH(A1,my_data[Particular]))),"")
I8	I8	=FILTER(UNIQUE($B$8:$B$1000),UNIQUE($B$8:$B$1000)<>0)
J8:J10	J8	=SUMIFS($C$8:$C$1000,$B$8:$B$1000,I8)
K8:K10	K8	=SUMIFS($F$8:$F$1000,$B$8:$B$1000,I8)
L8:L10	L8	=IFERROR(K8/J8, "")
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

As you seen in above excel in yellow cell J8 and L8 values are not filter as i filter data. J8 value should be 76 and L8 value should be 289.74. Kindly guide how to correct this. Thanks

 

[pankajgrover]

You already have the formula. You just need to move them to another location, so that they do not get hidden when you hide rows using the autofilter.

you means i have to cut and paste in another sheet ? which portion i have to move. here is below link which i have upload workbook. kindly look it and guide me..
My Excel full workbook

 

[Fluff]

As this is now a totally different question, it needs a new thread. Thanks

 

[pankajgrover]

You already have the formula. You just need to move them to another location, so that they do not get hidden when you hide rows using the autofilter.

From last 1 hrs i was looking your reply "move them to another location". Before, what i was doing that i was cut and pasting data into new Sheet from onwards G Col. where Col hv formula
" =MAP(INDEX(A8#,,1),LAMBDA(m,SUBTOTAL(103,m)))" but it did not worked at all, but just now finally i did not move this Col. and i cut and paste data into new Sheet from onwards I Col. Now filter working properly.. Thanks again..

 

[pankajgrover]

Hi, i have 1 system which have office 2021 pro, this below command not working there.. its working in 365. you have any alternate command which works same exactly on office 2021 pro also.. Thanks

=MAP(INDEX(A8#,,1),LAMBDA(m,SUBTOTAL(103,m)))

 

[Fluff]

You can use

=subtotal(103,A8)

and drag down.

 

[pankajgrover]

You can use

=subtotal(103,A8)

and drag down.

Perfect Working.. Thanks

 

[pankajgrover]

That's because those rows are hidden by the autofilter. You will need to move your formula to another area.

Hi as you describe i have moved this unique stocks average results to new sheet and its working good. Now after 2 - 3 days work, i realize i need 2 more columns and like this data ...
Particulars - Buy Qty - Buy Avg - Sell Qty - Sell Avg
Before all sticks go avg whether buy or sell..
Can you edit formula how to filter buy and sell qty or in averages... thanks

 

[Fluff]

As this is a totally different question, it needs a new thread.

 

[pankajgrover]

As this is a totally different question, it needs a new thread.

As per your advice, i created new thread... name " How to obtain average values on columns filter"