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

 

[Fluff]

Your filter formula is not filtering by date, it's filtering on col B.

 

[pankajgrover]

Your filter formula is not filtering by date, it's filtering on col B.

How to correct that if i want both filter A col and B col. In above example Row 7 have (Filter Ctrl+Shift+L) option is on. so A7 col date right side have little down-arrow button where i am selecting manually date to check data date wise also. you can guide and pls correct my formula and data.. should i send you my complete workbook ? here is no complete workbook attachment option ?

 

[Fluff]

It would have helped if you had said you were using the AutoFilter, especially as you are also using the Filter function.
In G8 put

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

and then use

=SUMIFS($C$8:$C$1000,$B$8:$B$1000,I8,$G$8:$G$1000,1)

 

[pankajgrover]

It would have helped if you had said you were using the AutoFilter, especially as you are also using the Filter function.
In G8 put

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

and then use

=SUMIFS($C$8:$C$1000,$B$8:$B$1000,I8,$G$8:$G$1000,1)

Thanks for helping me all the times. i am a basic user and i am still learning. Creating search box and auto filter syntax in A1, i just learn from youtube , may be its possible conflict with other filter.

Now i am on the bed , morning i will check your this commands and revert you. Thanks for giving me your valuable time.

 

[pankajgrover]

It would have helped if you had said you were using the AutoFilter, especially as you are also using the Filter function.
In G8 put

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

and then use

=SUMIFS($C$8:$C$1000,$B$8:$B$1000,I8,$G$8:$G$1000,1)

Wow its working. great . you are genius.. that is i want .. now data updated as i filter table. Thank you so much sir its was not easy but you did it with these command =MAP(INDEX(A8#,,1),LAMBDA(m,SUBTOTAL(103,m)))

 

[Fluff]

You're welcome & thanks for the feedback.

 

[pankajgrover]

You're welcome & thanks for the feedback.

Hi another issue comes i did not figure out , as you know i already (Filter Ctrl+Shift+L) option is on my header. there are 3 dates 04 Aug, 05 Aug, 06 Aug.. when i select all date or only 06/08/2024 right side unique stocks average comes , but when i filter on date and select 04 Aug or 05 Aug dates unique stocks average data not visible , here screen shot attached.. kindly look where i am mistaken .. Thanks

Shares Search Filter APPLE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		Advanced Search Bar - All Column Search
2
3
4
5									Unique Stocks Average
6			220	Average	1926.87	423911.30
7	Date	Particular	Qty	Buy/Sell	Price	Totals			Particular	Qty	Totals	AVG
8	06/08/2024	MICROSOFT	25	BUY	135.70	3392.50	1		MICROSOFT	66	286841.65	4346.09
9	06/08/2024	SAMSUNG	5	BUY	643.50	3217.50	1		SAMSUNG	11	19488.45	1771.68
10	06/08/2024	NETFLIX	11	BUY	3347.70	36824.70	1		NETFLIX	62	62878.95	1014.18
11	06/08/2024	APPLE	50	BUY	287.00	14350.00	1		APPLE	81	54702.25	675.34
12	06/08/2024	MICROSOFT	5	SELL	49899.90	249499.50	1				0.00
13	06/08/2024	SAMSUNG	1	BUY	5187.70	5187.70	1				0.00
14	06/08/2024	NETFLIX	25	BUY	135.84	3396.00	1				0.00
15	06/08/2024	APPLE	25	BUY	287.80	7195.00	1				0.00
16	06/08/2024	MICROSOFT	11	BUY	1610.65	17717.15	1				0.00
17	06/08/2024	SAMSUNG	4	BUY	2651.60	10606.40	1				0.00
18	05/08/2024	NETFLIX	1	BUY	2650.75	2650.75	1				0.00
19	05/08/2024	APPLE	1	SELL	475.50	475.50	1				0.00
20	05/08/2024	MICROSOFT	25	BUY	649.30	16232.50	1				0.00
21	05/08/2024	SAMSUNG	1	BUY	476.85	476.85	1				0.00
22	04/08/2024	NETFLIX	25	BUY	800.30	20007.50	1				0.00
23	04/08/2024	APPLE	5	BUY	6536.35	32681.75	1				0.00
search_bar

Cell Formulas
Range	Formula
C6,F6	C6	=SUBTOTAL(9,C8:C1000)
E6	E6	=F6/C6
A8:F23	A8	=FILTER(my_data,NOT(ISERROR(SEARCH(A1,my_data[Particular]))),"")
G8:G23	G8	=MAP(INDEX(A8#,,1),LAMBDA(m,SUBTOTAL(103,m)))
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)
K8	K8	=SUMIFS($F$8:$F$1000,$B$8:$B$1000,I8,$G$8:$G$1000,1)
L8:L23	L8	=IFERROR(K8/J8, "")
J9:J23	J9	=SUMIFS($C$8:$C$1000,$B$8:$B$1000,I9)
K9:K23	K9	=SUMIFS($F$8:$F$1000,$B$8:$B$1000,I9)
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

 

[Fluff]

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

 

[pankajgrover]

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

how will do that ?. can you correct my formula please. search box and autofilter if have to be removed for this purpose we can. but what commands should i use instead of these . please guide me.. Thanks

 

[Fluff]

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.