filter and sum changing values

[NeedExcelHelp2021]

Hello Hope Everyone Is Doing Well,


I was hoping someone might be kind enough to help me with a formula.

I'm trying to (Filter Cell A) and (Sum Cell E) but the values changes In Cell E when I Filter Cell A, I need a formula to filter and sum the changed values without have to filter.

I tried using =sum(filter but didn't work.

I'd be grateful for any help Thanks!

figure 1= unfiltered

Book1
	A	B	C	D	E	F	G	H	I	J
1								2009	2010	2011
2	column 1	column 2	column 3	column 4	$ 10,000	$ 10,000		$ 2,570	$ 2,946	$ 505
3	2009	3139	1924	0.10	$ 326.30	$ 10,326
4	2009	579	395	0.52	$ 302.73	$ 10,629
5	2009	-353	353	0.60	$ (212.58)	$ 10,416
6	2009	740	655	0.32	$ 235.36	$ 10,652
7	2009	2242	1020	0.21	$ 468.26	$ 11,120
8	2009	2972	1192	0.19	$ 554.51	$ 11,675
9	2009	1044	688	0.34	$ 354.31	$ 12,029
10	2009	2142	1720	0.14	$ 299.60	$ 12,329
11	2009	-1852	1852	0.13	$ (246.57)	$ 12,082
12	2009	2308	1142	0.21	$ 488.36	$ 12,570
13	2010	528	415	0.61	$ 319.86	$ 12,890
14	2010	1150	579	0.45	$ 512.04	$ 13,402
15	2010	1441	1112	0.24	$ 347.35	$ 13,750
16	2010	2569	1476	0.19	$ 478.63	$ 14,228
17	2010	1053	626	0.45	$ 478.67	$ 14,707
18	2010	-242	242	1.22	$ (294.14)	$ 14,413
19	2010	406	278	1.04	$ 420.98	$ 14,834
20	2010	660	372	0.80	$ 526.36	$ 15,360
21	2010	-322	322	0.95	$ (307.20)	$ 15,053
22	2010	545	354	0.85	$ 463.49	$ 15,516
23	2011	722	311	1.00	$ 720.44	$ 16,237
24	2011	1849	1350	0.24	$ 444.77	$ 16,682
25	2011	-651	651	0.51	$ (333.63)	$ 16,348
26	2011	281	260	1.26	$ 353.37	$ 16,701
27	2011	-568	568	0.59	$ (334.03)	$ 16,367
28	2011	-366	366	0.89	$ (327.34)	$ 16,040
29	2011	533	555	0.58	$ 308.08	$ 16,348
30	2011	-147	147	2.22	$ (326.96)	$ 16,021
Sheet1

Cell Formulas
Range	Formula
H2	H2	=SUM(FILTER(E3:E30,A3:A30=H1))
I2	I2	=SUM(FILTER(E3:E30,A3:A30=I1))
J2	J2	=SUM(FILTER(E3:E30,A3:A30=J1))
D3:D30	D3	=(F2*0.02)/C3
E3:E30	E3	=D3*B3
F3:F30	F3	=SUBTOTAL(9,$E$2:E3)

figure = filtered

Book1
	A	B	C	D	E	F	G	H	I	J
1								2009	2010	2011
2	column 1	column 2	column 3	column 4	$ 10,000	$ 10,000		$ 2,340	$ 2,344	$ 428
13	2010	528	415	0.48	$ 254.46	$ 10,254
14	2010	1150	579	0.35	$ 407.34	$ 10,662
15	2010	1441	1112	0.19	$ 276.32	$ 10,938
16	2010	2569	1476	0.15	$ 380.76	$ 11,319
17	2010	1053	626	0.36	$ 380.79	$ 11,700
18	2010	-242	242	0.97	$ (233.99)	$ 11,466
19	2010	406	278	0.82	$ 334.90	$ 11,801
20	2010	660	372	0.63	$ 418.73	$ 12,219
21	2010	-322	322	0.76	$ (244.39)	$ 11,975
22	2010	545	354	0.68	$ 368.72	$ 12,344
32
33
Sheet1

Cell Formulas
Range	Formula
H2	H2	=SUM(FILTER(E3:E30,A3:A30=H1))
I2	I2	=SUM(FILTER(E3:E30,A3:A30=I1))
J2	J2	=SUM(FILTER(E3:E30,A3:A30=J1))
D13:D22	D13	=(F12*0.02)/C13
E13:E22	E13	=D13*B13
F13:F22	F13	=SUBTOTAL(9,$E$2:E13)

 

[AhoyNC]

It looks like the SUBTOTAL formula you are using in column F is causing the problem.
Try changing it to:

=SUM($E$2:E3)

 

[NeedExcelHelp2021]

Thanks for replying


when i use SUM instead of SUBTOTAL the values don't change, I want the values to change.

In figure 1 the cells or unfiltered the total for 2010=2946
In figure 2 the cells or filtered the total for 2010=2344

i would like to have a formula get this result In figure 2 without have to filter the cells , if this is possible.