Need "SUMPRODUCT, COUNTIF" non-volatile formula to filter data by year

[motilulla]

Hello,

In the column "C" I got year and in the column "D" the numbers "0 to 6"
In the cells G4:M4 numbers "0 to 6"
And in the cells G5:M5 got the formula below

G5 = SUMPRODUCT(SUBTOTAL(3,OFFSET($D$6,ROW($D$6:$D$53)-ROW($D$6),0,1)),--($D$6:$D$53=G4))

And copied across till M5

The above "SUMPRODUCT, COUNTIF" formula is working fine when I filter the year but...
Please I need this formula can be converted a non-volatile formula?

Example image is attached for more details

*	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4			Year	Numbers		Numbers	0	1	2	3	4	5	6
5			Year	Numbers		Countif	1	7	15	16	4	3	2
6			2008	0
7			2010	1
8			2007	1
9			2010	1
10			2009	2
11			2009	2
12			2008	1
13			2006	2
14			2006	2
15			2010	1
16			2006	2
17			2005	5
18			2010	2
19			2005	3
20			2005	3
21			2005	2
22			2010	3
23			2005	3
24			2007	1
25			2010	2
26			2006	2
27			2009	2
28			2006	6
29			2008	3
30			2007	2
31			2009	3
32			2006	3
33			2009	1
34			2008	2
35			2006	5
36			2009	2
37			2006	3
38			2005	3
39			2006	2
40			2008	3
41			2009	3
42			2008	2
43			2006	3
44			2010	3
45			2010	6
46			2006	4
47			2010	4
48			2010	3
49			2008	4
50			2010	4
51			2008	5
52			2009	3
53			2005	3
54
55
56

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[maabadi]

Why you don't Use:

= SUMPRODUCT(CountA(OFFSET($D$6,ROW($D$6:$D$53)-ROW($D$6),0,1)),--($D$6:$D$53=G4))

 

[Fluff]

You could use a helper column

+Fluff 1.xlsm
	C	D	E	F	G	H	I	J	K	L	M
4	Year	Numbers		Numbers	0	1	2	3	4	5	6
5	Year	Numbers		Countif	1	7	15	16	4	3	2
6	2008	0	1
7	2010	1	1
8	2007	1	1
9	2010	1	1
10	2009	2	1
11	2009	2	1
12	2008	1	1
13	2006	2	1
14	2006	2	1
15	2010	1	1
16	2006	2	1
17	2005	5	1
18	2010	2	1
19	2005	3	1
20	2005	3	1
21	2005	2	1
22	2010	3	1
23	2005	3	1
24	2007	1	1
25	2010	2	1
26	2006	2	1
27	2009	2	1
28	2006	6	1
29	2008	3	1
30	2007	2	1
31	2009	3	1
32	2006	3	1
33	2009	1	1
34	2008	2	1
35	2006	5	1
36	2009	2	1
37	2006	3	1
38	2005	3	1
39	2006	2	1
40	2008	3	1
41	2009	3	1
42	2008	2	1
43	2006	3	1
44	2010	3	1
45	2010	6	1
46	2006	4	1
47	2010	4	1
48	2010	3	1
49	2008	4	1
50	2010	4	1
51	2008	5	1
52	2009	3	1
53	2005	3	1
Data

Cell Formulas
Range	Formula
G5:M5	G5	=SUMPRODUCT(--($E$6:$E$53=1),--($D$6:$D$53=G4))
E6:E53	E6	=SUBTOTAL(3,D6)

 

[motilulla]

Why you don't Use:

= SUMPRODUCT(CountA(OFFSET($D$6,ROW($D$6:$D$53)-ROW($D$6),0,1)),--($D$6:$D$53=G4))

maabadi, I applied formula but getting an error #VALUE!, function "OFFSET" is volatile I am looking formula to be replaced non-volatile

Please check, thank you for your help

Kind Regards,
Moti

 

[motilulla]

You could use a helper column

+Fluff 1.xlsm
	C	D	E	F	G	H	I	J	K	L	M
4	Year	Numbers		Numbers	0	1	2	3	4	5	6
5	Year	Numbers		Countif	1	7	15	16	4	3	2
6	2008	0	1
7	2010	1	1
8	2007	1	1
9	2010	1	1
10	2009	2	1
11	2009	2	1
12	2008	1	1
13	2006	2	1
14	2006	2	1
15	2010	1	1
16	2006	2	1
17	2005	5	1
18	2010	2	1
19	2005	3	1
20	2005	3	1
21	2005	2	1
22	2010	3	1
23	2005	3	1
24	2007	1	1
25	2010	2	1
26	2006	2	1
27	2009	2	1
28	2006	6	1
29	2008	3	1
30	2007	2	1
31	2009	3	1
32	2006	3	1
33	2009	1	1
34	2008	2	1
35	2006	5	1
36	2009	2	1
37	2006	3	1
38	2005	3	1
39	2006	2	1
40	2008	3	1
41	2009	3	1
42	2008	2	1
43	2006	3	1
44	2010	3	1
45	2010	6	1
46	2006	4	1
47	2010	4	1
48	2010	3	1
49	2008	4	1
50	2010	4	1
51	2008	5	1
52	2009	3	1
53	2005	3	1
Data

Cell Formulas
Range	Formula
G5:M5	G5	=SUMPRODUCT(--($E$6:$E$53=1),--($D$6:$D$53=G4))
E6:E53	E6	=SUBTOTAL(3,D6)

Fluff, interesting formula, yes it worked perfect, and off course it is non-volatile as I request.

Just a question does it could be other option which work without the helper column if not I will use this

Thank you so much for your help

Kind Regards,
Moti

 

[Fluff]

As far as I know, you either need the helper column, or the Offset function.

 

[motilulla]

As far as I know, you either need the helper column, or the Offset function.

Fluff, thank you for the answer, let wait till tomorrow may someone has solution without helper column

Kind Regards,
Moti

 

[motilulla]

As far as I know, you either need the helper column, or the Offset function.

Fluff, thank you for the answer, let wait till tomorrow may someone has solution without helper column

Kind Regards,
Moti

Thank you very much Fluff, after you gave a solution nearly 28 views has been more it seems that there is only one ways as you describe to fix it non-volatile has to be with helper column, yes I adapted the formula in various situations and really it is working like magic absolutely perfect - you are a star I can say it is solved.

Have a nice day and Good Luck

Kind Regards,
Moti

 

[Fluff]

Glad to help & thanks for the feedback.