COUNTIF with multilayered dropdown selections

[LWLD]

Hi all,

I am a complete novice when it comes to Excel, I manage to do most things via Google searches and forums like this.

I have complied a sheet which has data values from 0 - 4 and managed to get a table to count up the number of each value grade (0 - 4). However, I am now trying to get this to allow me to filter these results via dropdown selections. I have included a minisheet to better explain/demonstrate this as I fear I will complicate it too much if I try. The 'Number of Marks at Grade' table is set up to count up the total across each donor value. I'd like to use the filter at the side to narrow the results more.

If anyone can assist i would be grateful, if not, thank you to those to took the time to read this.

Book1.ods
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
66	5	Fired	9mm	Pinch	Washed	1	0	0	0	0	0	0	0	0	0
67					Unwashed	0	0	1	0	1	0	0	0	0	0
68				Load	Washed	0	0	0	0	0	0	0	0	0	1
69					Unwashed	1	1	0	1	0	1	0	0	1	0
70			.45s	Pinch	Washed
71					Unwashed
72				Load	Washed
73					Unwashed
74		Unfired	9mm	Pinch	Washed	1	1	2	1	1	2	1	0	1	0
75					Unwashed	3	4	3	4	3	3	3	3	2	3
76				Load	Washed	0	1	0	1	0	0	0	0	1	0
77					Unwashed	1	1	1	0	0	1	2	1	0	1
78			.45s	Pinch	Washed	0	1	2	1	3	2	1	1	1	1
79					Unwashed	3	3	4	3	4	3	3	3	3	3
80				Load	Washed	1	1	1	0	0	1	1	1	1	0
81					Unwashed	2	2	1	1	2	1	1	2	2	1									Donor	Number of Marks at Grade							Filter
82	6	Fired	9mm	Pinch	Washed	0	0	0	0	0	0	0	0	0	0										0	1	2	3	4	Total		Condition
83					Unwashed	0	0	0	0	0	0	0	0	1	0									1	62	43	14	1	0	120		Caliber
84				Load	Washed	0	1	0	0	0	1	0	0	1	0									2	76	40	4	0	0	120		Contact
85					Unwashed	0	0	1	1	0	0	0	1	0	0									3	50	43	26	1	0	120		Surface
86			.45s	Pinch	Washed																			4	7	25	44	18	26	120
87					Unwashed																			5	47	42	11	16	4	120
88				Load	Washed																			6	111	8	1	0	0	120
89					Unwashed																			7	29	46	39	6	0	120
90		Unfired	9mm	Pinch	Washed	0	0	0	0	0	0	0	0	0	0									8	53	43	24	0	0	120
91					Unwashed	0	0	0	0	0	0	0	0	0	0									9	34	57	18	5	6	120
92				Load	Washed	0	0	0	0	0	0	0	0	0	0									10	20	35	33	13	19	120
93					Unwashed	0	0	0	0	0	0	0	0	0	1									Total	489	382	214	60	55	1200
94			.45s	Pinch	Washed	0	0	0	0	0	0	0	0	0	0									%age	41%	32%	18%	5%	5%
95					Unwashed	0	0	0	0	0	0	0	0	0	0
96				Load	Washed	0	0	0	0	0	0	0	0	0	0
97					Unwashed	0	0	0	0	0	0	0	2	0	0
Gradings_(2)

Cell Formulas
Range	Formula
Y83	Y83	=COUNTIF(F2:O17,"0")
Z83	Z83	=COUNTIF(F2:O17,"1")
AA83	AA83	=COUNTIF(F2:O17,"2")
AB83	AB83	=COUNTIF(F2:O17,"3")
AC83	AC83	=COUNTIF(F2:O17,"4")
AD83:AD92	AD83	=SUM(Y83:AC83)
Y84	Y84	=COUNTIF(F18:O33,"0")
Z84	Z84	=COUNTIF(F18:O33,"1")
AA84	AA84	=COUNTIF(F18:O33,"2")
AB84	AB84	=COUNTIF(F18:O33,"3")
AC84	AC84	=COUNTIF(F18:O33,"4")
Y85	Y85	=COUNTIF(F34:O49,"0")
Z85	Z85	=COUNTIF(F34:O49,"1")
AA85	AA85	=COUNTIF(F34:O49,"2")
AB85	AB85	=COUNTIF(F34:O49,"3")
AC85	AC85	=COUNTIF(F34:O49,"4")
Y86	Y86	=COUNTIF(F50:O65,"0")
Z86	Z86	=COUNTIF(F50:O65,"1")
AA86	AA86	=COUNTIF(F50:O65,"2")
AB86	AB86	=COUNTIF(F50:O65,"3")
AC86	AC86	=COUNTIF(F50:O65,"4")
Y87	Y87	=COUNTIF(F66:O81,"0")
Z87	Z87	=COUNTIF(F66:O81,"1")
AA87	AA87	=COUNTIF(F66:O81,"2")
AB87	AB87	=COUNTIF(F66:O81,"3")
AC87	AC87	=COUNTIF(F66:O81,"4")
Y88	Y88	=COUNTIF(F82:O97,"0")
Z88	Z88	=COUNTIF(F82:O97,"1")
AA88	AA88	=COUNTIF(F82:O97,"2")
AB88	AB88	=COUNTIF(F82:O97,"3")
AC88	AC88	=COUNTIF(F82:O97,"4")
Y89	Y89	=COUNTIF(F98:O113,"0")
Z89	Z89	=COUNTIF(F98:O113,"1")
AA89	AA89	=COUNTIF(F98:O113,"2")
AB89	AB89	=COUNTIF(F98:O113,"3")
AC89	AC89	=COUNTIF(F98:O113,"4")
Y90	Y90	=COUNTIF(F114:O129,"0")
Z90	Z90	=COUNTIF(F114:O129,"1")
AA90	AA90	=COUNTIF(F114:O129,"2")
AB90	AB90	=COUNTIF(F114:O129,"3")
AC90	AC90	=COUNTIF(F114:O129,"4")
Y91	Y91	=COUNTIF(F130:O145,"0")
Z91	Z91	=COUNTIF(F130:O145,"1")
AA91	AA91	=COUNTIF(F130:O145,"2")
AB91	AB91	=COUNTIF(F130:O145,"3")
AC91	AC91	=COUNTIF(F130:O145,"4")
Y92	Y92	=COUNTIF(F146:O161,"0")
Z92	Z92	=COUNTIF(F146:O161,"1")
AA92	AA92	=COUNTIF(F146:O161,"2")
AB92	AB92	=COUNTIF(F146:O161,"3")
AC92	AC92	=COUNTIF(F146:O161,"4")
Y93:AC93	Y93	=SUM(Y83:Y92)
AD93	AD93	=IF(SUM(AD83:AD92)=SUM(Y93:AC93),(SUM(AD83:AD92)+SUM(Y93:AC93))/2,0)
Y94	Y94	=ROUND(Y93/AD93*100,0)&"%"
Z94	Z94	=ROUND(Z93/AD93*100,0)&"%"
AA94	AA94	=ROUND(AA93/AD93*100,0)&"%"
AB94	AB94	=ROUND(AB93/AD93*100,0)&"%"
AC94	AC94	=ROUND(AC93/AD93*100,0)&"%"

Cells with Data Validation
Cell	Allow	Criteria
F66:O97	List	0,1,2,3,4

 

[LWLD]

Hi @Eric W, I played around and it's working like a dream.

Cannot thank you enough.

 

[Eric W]

Glad it works for you!