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

 

[Snakehips]

@LWLD Welcome.

I can see nothing in the above data that enables me to relate to the filters.
You may need to clarify, in order to get a meaningful response.

 

[Oaktree]

What does the data that feeds columns A:O look like? If that's a normalized data table, the answer to your question might be as simple as replacing what's currently in A:O with a pivot table and looking at the subtotals it would natively include.

 

[Eric W]

I think Oaktree's idea of normalizing the data and using a pivot table has a lot of merit. But if you are stuck with the layout you have, there might be options. The fact that there are 2 options for each filter makes a binary search possible. Consider:

Book1
	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
1	Donor	Condition	Calibur	Contact	Surface												Donor	Number of Marks at Grade							Filter		Options	Helper
2	1	Fired	9mm	Pinch	Washed	1	0	0	0	0	0	0	0	0	0			0	1	2	3	4	Total		Condition	Unfired	Fired/Unfired/All	1
3					Unwashed	0	0	1	0	1	0	0	0	0	0		1	0	5	5	8	2	20		Caliber	.45s	9mm/.45s/All	1
4				Load	Washed	0	0	0	0	0	0	0	0	0	1		2	19	0	1	0	0	20		Contact	All	Pinch/Load/All	1
5					Unwashed	1	1	0	1	0	1	0	0	1	0		3	0	5	5	8	2	20		Surface	Unwashed	Washed/Unwashed/All	1
6			.45s	Pinch	Washed												4	19	0	1	0	0	20
7					Unwashed												5	0	5	5	8	2	20
8				Load	Washed												6	19	0	1	0	0	20
9					Unwashed												7	19	0	1	0	0	20
10		Unfired	9mm	Pinch	Washed	1	1	2	1	1	2	1	0	1	0		8	19	0	1	0	0	20
11					Unwashed	3	4	3	4	3	3	3	3	2	3		9	19	0	1	0	0	20
12				Load	Washed	0	1	0	1	0	0	0	0	1	0		10	19	0	1	0	0	20
13					Unwashed	1	1	1	0	0	1	2	1	0	1		Total	133	15	22	24	6	200
14			.45s	Pinch	Washed	0	1	2	1	3	2	1	1	1	1		%age	67%	8%	11%	12%	3%
15					Unwashed	3	3	4	3	4	3	3	3	3	3
16				Load	Washed	1	1	1	0	0	1	1	1	1	0
17					Unwashed	2	2	1	1	2	1	1	2	2	1
18	2	Fired	9mm	Pinch	Washed	0	0	0	0	0	0	0	0	0	0
19					Unwashed	0	0	0	0	0	0	0	0	1	0
20				Load	Washed	0	1	0	0	0	1	0	0	1	0
21					Unwashed	0	0	1	1	0	0	0	1	0	0
22			.45s	Pinch	Washed
23					Unwashed
24				Load	Washed
25					Unwashed
26		Unfired	9mm	Pinch	Washed	0	0	0	0	0	0	0	0	0	0
27					Unwashed	0	0	0	0	0	0	0	0	0	0
28				Load	Washed	0	0	0	0	0	0	0	0	0	0
29					Unwashed	0	0	0	0	0	0	0	0	0	1
30			.45s	Pinch	Washed	0	0	0	0	0	0	0	0	0	0
31					Unwashed	0	0	0	0	0	0	0	0	0	0
32				Load	Washed	0	0	0	0	0	0	0	0	0	0
33					Unwashed	0	0	0	0	0	0	0	2	0	0
Sheet7

Cell Formulas
Range	Formula
R3:V12	R3	=LET(donor,INT((ROW($F$2:$F$161)+14)/16),bin,RIGHT(DEC2BIN(ROW($F$2:$F$161)-2,10),4),cond,LEFT(bin,1),cal,MID(bin,2,1),cont,MID(bin,3,1),surf,RIGHT(bin,1),fil,FILTER($F$2:$O$161,(donor=ROWS($R$3:$R3))*((cond=$AB$2)+($Z$2="All"))*((cal=$AB$3)+($Z$3="All"))*((cont=$AB$4)+($Z$4="All"))*((surf=$AB$5)+($Z$5="All"))),SUMPRODUCT(--(fil&""=R$2&"")))
W3:W13	W3	=SUM(R3:V3)
R13:V13	R13	=SUM(R3:R12)
AB2	AB2	=IF(Z2="Fired","0","1")
AB3	AB3	=IF(Z3="9mm","0","1")
AB4	AB4	=IF(Z4="Pinch","0","1")
AB5	AB5	=IF(Z5="Washed","0","1")
R14:V14	R14	=ROUND(R13/$W13,2)

I assume you'd have a dropdown in cells Z2:Z5, and based on the options selected, the values in R3:V12 would adjust. The formula is pretty complicated though, and requires the helper cells in AB2:AB5.

 

[LWLD]

Thanks for the responses thus far.

@Snakehips Thank you for the warm welcome, and apologies, the data for the dropdown selections mentioned was on another sheet so didn't get listed. The filter selections are as follows:
Condition: All/Fired/Unfired
Caliber: All/9mm/.45s
Contact: All/Pinch/Load
Surface: All/Washed/Unwashed

@Oaktree Thanks for the suggestion, however, a Pivot table is not valid, perhaps because of the layout as @Eric W mentioned.

@Eric W Thanks for your efforts here. To clarify, Donor 1/Grade 0 R3 would only encompass data from F2:O17 as opposed to F2:F161 or F2:O161, sorry for the confusion. You mention two option per filter field, would that not actually be three options as there's the two options plus the sum of those two?
Your assumption about my dropdowns in cells Z2:Z5 is correct, though it is disheartening to hear that the formula to achieve this is complicated. If that is the case, I may have to explore other options. But, I would like to take the time to thank you for all the help you have provided. It warms my heard to find a community do willing to assist novices in the field, it is very much appreciated, and I apologise if anyone feels their time has been wasted.

 

[Eric W]

Consider this (I only showed 2 donors in this mini-sheet, but the other 8 are included in the formulas):

Book1
	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
1	Donor	Condition	Calibur	Contact	Surface												Donor	Number of Marks at Grade							Filter		Options	Helper
2	1	Fired	9mm	Pinch	Washed	1	0	0	0	0	0	0	0	0	0			0	1	2	3	4	Total		Condition	Fired	Fired/Unfired/All	0
3					Unwashed	0	0	1	0	1	0	0	0	0	0		1	13	7	0	0	0	20		Caliber	9mm	9mm/.45s/All	0
4				Load	Washed	0	0	0	0	0	0	0	0	0	1		2	16	4	0	0	0	20		Contact	All	Pinch/Load/All	?
5					Unwashed	1	1	0	1	0	1	0	0	1	0		3	13	7	0	0	0	20		Surface	Unwashed	Washed/Unwashed/All	1
6			.45s	Pinch	Washed												4	16	4	0	0	0	20					00?1
7					Unwashed												5	13	7	0	0	0	20
8				Load	Washed												6	16	4	0	0	0	20
9					Unwashed												7	16	4	0	0	0	20
10		Unfired	9mm	Pinch	Washed	1	1	2	1	1	2	1	0	1	0		8	16	4	0	0	0	20
11					Unwashed	3	4	3	4	3	3	3	3	2	3		9	16	4	0	0	0	20
12				Load	Washed	0	1	0	1	0	0	0	0	1	0		10	16	4	0	0	0	20
13					Unwashed	1	1	1	0	0	1	2	1	0	1		Total	151	49	0	0	0	200
14			.45s	Pinch	Washed	0	1	2	1	3	2	1	1	1	1		%age	76%	25%	0%	0%	0%
15					Unwashed	3	3	4	3	4	3	3	3	3	3
16				Load	Washed	1	1	1	0	0	1	1	1	1	0
17					Unwashed	2	2	1	1	2	1	1	2	2	1
18	2	Fired	9mm	Pinch	Washed	0	0	0	0	0	0	0	0	0	0
19					Unwashed	0	0	0	0	0	0	0	0	1	0
20				Load	Washed	0	1	0	0	0	1	0	0	1	0
21					Unwashed	0	0	1	1	0	0	0	1	0	0
22			.45s	Pinch	Washed
23					Unwashed
24				Load	Washed
25					Unwashed
26		Unfired	9mm	Pinch	Washed	0	0	0	0	0	0	0	0	0	0
27					Unwashed	0	0	0	0	0	0	0	0	0	0
28				Load	Washed	0	0	0	0	0	0	0	0	0	0
29					Unwashed	0	0	0	0	0	0	0	0	0	1
30			.45s	Pinch	Washed	0	0	0	0	0	0	0	0	0	0
31					Unwashed	0	0	0	0	0	0	0	0	0	0
32				Load	Washed	0	0	0	0	0	0	0	0	0	0
33					Unwashed	0	0	0	0	0	0	0	2	0	0
Sheet7

Cell Formulas
Range	Formula
R3:V12	R3	=SUMPRODUCT(($F$2:$O$161&""=R$2&"")*(INT((ROW($F$2:$F$161)+14)/16)=ROWS($R$3:$R3))*ISNUMBER(SEARCH($AB$6,DEC2BIN(MOD(ROW($F$2:$F$161)-2,16),4))))
W3:W13	W3	=SUM(R3:V3)
R13:V13	R13	=SUM(R3:R12)
AB2	AB2	=MID("01?",FIND(LEFT(Z2),"FUA"),1)
AB3	AB3	=MID("01?",FIND(LEFT(Z3),"9.A"),1)
AB4	AB4	=MID("01?",FIND(LEFT(Z4),"PLA"),1)
AB5	AB5	=MID("01?",FIND(LEFT(Z5),"WUA"),1)
AB6	AB6	=AB2&AB3&AB4&AB5
R14:V14	R14	=ROUND(R13/$W13,2)

I was playing around with it a bit more, and with a different choice of the helper formula, I managed to significantly shorten the formula. The helper formulas (AB2:AB5) return a 0 for the first option, a 1 for the second option, and a ? for either. Then the AB6 formula concatenates them.


=SUMPRODUCT(($F$2:$O$161&""=R$2&"")*(INT((ROW($F$2:$F$161)+14)/16)=ROWS($R$3:$R3))*ISNUMBER(SEARCH($AB$6,DEC2BIN(MOD(ROW($F$2:$F$161)-2,16),4))))

As far as the main formula, the part in red checks to see which cells in your range (F2:O161, enough for 10 donors) match the grade marks (R2:V2). The part in blue excludes everything except the donor you want (G3:G12). The part in green takes the row numbers (F2:F161), converts them to binary, takes the last 4 binary digits, and compares that with the AB6 filter, which should match the filters you select in Z2:Z5. The ? works as a wildcard, matching 0 or 1.

Still pretty complicated, but not too bad. This version also does not require any of the newer functions. Hope this works for you.

 

[LWLD]

Morning,

Thanks for getting back to me @Eric W . I've tried the formula and am getting "value is not available to the formula or function" message. I changed cell values to suit my particular sheet, but nothing. I am a bit confused by the green G2:G12 as I don't see it in the formula, but also because it is only part of Donor 1's number 2 bullet/casing allocation. Each Donor (cell A values) has 10 bullets/casings for each filter condition (rows 2:17 for donor 1). F2:F161 would just be looking at a donor's first casing, or am I grasping this wrong?

The filter part is working great, though, instead of displaying 0/1/? as it does in your cell AB6 mine displays the text equivalents.

Thanks again

 

[Eric W]

I'm going to have to explain a bit more about how the formula works. The hardest part about your layout is the use of merged cells. A2:A17, B2:B9, C6:C9, etc. Merged cells are really hard for formulas to handle. For the A2:A17 merged cell, in a formula A2 would have 1 in it, and A3:A17 would be empty. There are complicated, calculation-intensive ways around that, but the usual rule of thumb is to avoid them whenever possible. So your sheet would look something like:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Donor	Condition	Calibur	Contact	Surface
2	1	Fired	9mm	Pinch	Washed	1	0	0	0	0	0	0	0	0	0
3	1	Fired	9mm	Pinch	Unwashed	0	0	1	0	1	0	0	0	0	0
4	1	Fired	9mm	Load	Washed	0	0	0	0	0	0	0	0	0	1
5	1	Fired	9mm	Load	Unwashed	1	1	0	1	0	1	0	0	1	0
6	1	Fired	.45s	Pinch	Washed
7	1	Fired	.45s	Pinch	Unwashed
8	1	Fired	.45s	Load	Washed
9	1	Fired	.45s	Load	Unwashed
10	1	Unfired	9mm	Pinch	Washed	1	1	2	1	1	2	1	0	1	0
11	1	Unfired	9mm	Pinch	Unwashed	3	4	3	4	3	3	3	3	2	3
12	1	Unfired	9mm	Load	Washed	0	1	0	1	0	0	0	0	1	0
13	1	Unfired	9mm	Load	Unwashed	1	1	1	0	0	1	2	1	0	1
14	1	Unfired	.45s	Pinch	Washed	0	1	2	1	3	2	1	1	1	1
15	1	Unfired	.45s	Pinch	Unwashed	3	3	4	3	4	3	3	3	3	3
16	1	Unfired	.45s	Load	Washed	1	1	1	0	0	1	1	1	1	0
17	1	Unfired	.45s	Load	Unwashed	2	2	1	1	2	1	1	2	2	1
Sheet11

Not as nice to look at perhaps, but easier to write formulas for. If you format some of the cells to hide the contents, maybe this:

Book1
	A	B	C	D	E	F
1	Donor	Condition	Calibur	Contact	Surface
2	1	Fired	9mm	Pinch	Washed	1
3					Unwashed	0
4				Load	Washed	0
5					Unwashed	1
6			.45s	Pinch	Washed
7					Unwashed
8				Load	Washed
9					Unwashed
10		Unfired	9mm	Pinch	Washed	1
11					Unwashed	3
12				Load	Washed	0
13					Unwashed	1
14			.45s	Pinch	Washed	0
15					Unwashed	3
16				Load	Washed	1
17					Unwashed	2
Sheet11

The other values are still there, just hidden.

But I realized that your layout follows a particular pattern. The donor takes up 16 rows, the Condition toggles between Fired and Unfired every 8 rows, Calibur toggles between 9mm and .45s every 4 rows, Contact toggles between Pinch and Load every 2 rows, and Surface toggles between Washed and Unwashed every row. If you understand binary counting, that's exactly what's happening. If not, see here:

Book1
	A	B	C	D	E											P
1	Donor	Condition	Calibur	Contact	Surface											Filter Match
2	1	Fired	9mm	Pinch	Washed											0000
3					Unwashed											0001
4				Load	Washed											0010
5					Unwashed											0011
6			.45s	Pinch	Washed											0100
7					Unwashed											0101
8				Load	Washed											0110
9					Unwashed											0111
10		Unfired	9mm	Pinch	Washed											1000
11					Unwashed											1001
12				Load	Washed											1010
13					Unwashed											1011
14			.45s	Pinch	Washed											1100
15					Unwashed											1101
16				Load	Washed											1110
17					Unwashed											1111
18	2	Fired	9mm	Pinch	Washed											0000
19					Unwashed											0001
20				Load	Washed											0010
21					Unwashed											0011
22			.45s	Pinch	Washed											0100
23					Unwashed											0101
24				Load	Washed											0110
25					Unwashed											0111
26		Unfired	9mm	Pinch	Washed											1000
27					Unwashed											1001
28				Load	Washed											1010
29					Unwashed											1011
30			.45s	Pinch	Washed											1100
31					Unwashed											1101
32				Load	Washed											1110
33					Unwashed											1111
Sheet7

Cell Formulas
Range	Formula
P2:P33	P2	=DEC2BIN(MOD(ROW()-2,16),4)

The formula in P2 takes the row number and subtracts 2 so that it starts at 0. Then we convert it to binary (the DEC2BIN function), and just take the last 4 digits. Now each of the 4 digits in the P2 formula corresponds to one of your conditions. The Condition corresponds to the first digit. If the first digit is 0, that means Fired. If the first digit is 1, that means Unfired. For the second digit, if it's a 0, that means 9mm, if it's a 1 that means .45s. The other 2 work the same way. So what I'm doing is instead of looking for Fired, or Load, or whatever in the titles, I'm looking at the position of the row on the sheet to decide which of the conditions are being used on a given line. So when I created the filter in AB6, it has to be a combination of 1s and 0s and ?s. A filter of 00?1 means Fired, 9mm, Unwashed, and Contact=Any, or rows 3 and 5, 19 and 21, 35 and 23, etc.

I mentioned that the binary counting has to start at 0. The latest formula assumed that the data started on row 2, and I hardcoded a 2 in the formula in 2 places, but if you start on a different row, it would cause problems. This version should take care of that:

Book1
	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Donor	Number of Marks at Grade							Filter		Options	Helper
2		0	1	2	3	4	Total		Condition	Fired	Fired/Unfired/All	0
3	1	13	7	0	0	0	20		Caliber	9mm	9mm/.45s/All	0
4	2	16	4	0	0	0	20		Contact	All	Pinch/Load/All	?
5	3	13	7	0	0	0	20		Surface	Unwashed	Washed/Unwashed/All	1
6	4	16	4	0	0	0	20					00?1
7	5	13	7	0	0	0	20
8	6	16	4	0	0	0	20
9	7	16	4	0	0	0	20
10	8	16	4	0	0	0	20
11	9	16	4	0	0	0	20
12	10	16	4	0	0	0	20
13	Total	151	49	0	0	0	200
14	%age	76%	25%	0%	0%	0%
Sheet7

Cell Formulas
Range	Formula
R3:V12	R3	=SUMPRODUCT(($F$2:$O$161&""=R$2&"")*(INT((ROW($F$2:$F$161)+16-ROW($F$2))/16)=ROWS($R$3:$R3))*ISNUMBER(SEARCH($AB$6,DEC2BIN(MOD(ROW($F$2:$F$161)-ROW($F$2),16),4))))
W3:W13	W3	=SUM(R3:V3)
R13:V13	R13	=SUM(R3:R12)
AB2	AB2	=MID("01?",FIND(LEFT(Z2),"FUA"),1)
AB3	AB3	=MID("01?",FIND(LEFT(Z3),"9.A"),1)
AB4	AB4	=MID("01?",FIND(LEFT(Z4),"PLA"),1)
AB5	AB5	=MID("01?",FIND(LEFT(Z5),"WUA"),1)
AB6	AB6	=AB2&AB3&AB4&AB5
R14:V14	R14	=ROUND(R13/$W13,2)

The way the donor is selected is similar, although a bit easier. We just take the starting row, adjust it to 0, then add 16. Then divide by 16 and drop any fraction. This is also just done by position, not by looking for the name. So the order of the names in column A must be the same order as the names in column Q, just spread out more.

Anyway, a bit long winded, but I hope this makes it clearer, and that you can adjust it to your sheet.

 

[LWLD]

@Eric W I cannot thank you enough for taking the time to explain it in such a though, and relatable manner.

I have the formula working now so that it doesn't throw out an error, however, the values are particularly low. Is this possibly due to the SUMPRODUCT function adding the values rather than counting them?

Thanks

 

[Eric W]

No, the SUMPRODUCT just counts the matching values. I had to use a trick (using the &"") to avoid counting empty spaces as 0s though. The best thing to do is to test it out. Pick a donor, set a few filters, and manually count the result and see if it matches. Try a few different filters. If you find one that doesn't match, use the XL2BB to show it, and explain where it goes wrong.