CountIFS with Multiple Criteria

[GMC The Macro Man]

Hi there,
Could someone please help me with this formula. I need to count the number of times a number or letter appears in columns C, E & G based on the CLASS in K2.
The attached sheet shows a number of students and their class and in columns C,E & G they have a value which I need to be totaled and entered in to cells N2:Q2
I would normally just take this as a range but its proving difficult as i only want these specific columns

Any help would be appreciated
GMC

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Name	Class	H1	H2	T1	T2	R1	R2						1	2	3	M
2	Aidan	P1	1	8	M	6	1	W		CLASS	P3		COUNT	3	4	4	3
3	Amelia	P2	2	B	M	5	1	S
4	Andrew	P3	3	C	3	9	1
5	Ava	P4	M	8	2	H	2	4
6	Clara	P1	1	7	1	U	2			K2 = CHOOSE CLASS
7	Dillon	P2	2	1A	3		3	4		N2:Q2 =COUNT COLUMNS C,E and G
8	Emily	P3	3		M		1
9	Freddie	P4	M		2		3
10	Georgie	P1	M	3	M	2	2	7
11	Jacob	P2	2	5	M		2
12	Kaiden	P3	3	T	1	1	M	3
13	Kayla	P4	M		1		M	A
14	Layla	P1	1		1	4	1	A
15	Lena	P2	3	8	2		1
16	Matthew	P3	2	8	2	G	2	M
17	Millie	P4	M		M	T	1
18	Quinn	P1	1	B	3		M	E
19	Ruaridh	P2	2	C	M	J	3	E
20	Sam	P3	M		2		3	3
21	Saoirse	P4	M		2	P	3
22
23
Sheet1

 

[etaf]

how about
=COUNTIFS($C$2:$C$21,N$1,$B$2:$B$21,$K$2)+COUNTIFS($E$2:$E$21,N$1,$B$2:$B$21,$K$2)+COUNTIFS($G$2:$G$21,N$1,$B$2:$B$21,$K$2)

BUT i make "3" result 5

maybe a better way with 2021 using filter() or let() maybe

Book6
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Name	Class	H1	H2	T1	T2	R1	R2						1	2	3	M
2	Aidan	P1	1	8	M	6	1	W		CLASS	P3		COUNT	3	4	4	3
3	Amelia	P2	2	B	M	5	1	S						3	4	5	3
4	Andrew	P3	3	C	3	9	1
5	Ava	P4	M	8	2	H	2	4
6	Clara	P1	1	7	1	U	2			K2 = CHOOSE CLASS
7	Dillon	P2	2	1A	3		3	4		N2:Q2 =COUNT COLUMNS C,E and G
8	Emily	P3	3		M		1
9	Freddie	P4	M		2		3
10	Georgie	P1	M	3	M	2	2	7
11	Jacob	P2	2	5	M		2
12	Kaiden	P3	3	T	1	1	M	3
13	Kayla	P4	M		1		M	A
14	Layla	P1	1		1	4	1	A
15	Lena	P2	3	8	2		1
16	Matthew	P3	2	8	2	G	2	M
17	Millie	P4	M		M	T	1
18	Quinn	P1	1	B	3		M	E
19	Ruaridh	P2	2	C	M	J	3	E
20	Sam	P3	M		2		3	3
21	Saoirse	P4	M		2	P	3
Sheet1

Cell Formulas
Range	Formula
N3:Q3	N3	=COUNTIFS($C$2:$C$21,N$1,$B$2:$B$21,$K$2)+COUNTIFS($E$2:$E$21,N$1,$B$2:$B$21,$K$2)+COUNTIFS($G$2:$G$21,N$1,$B$2:$B$21,$K$2)

 

[Fluff]

Another option

=SUMPRODUCT(($B$2:$B$100=$K$2)*(RIGHT($C$1:$H$1)="1")*($C$2:$H$100=N1))

 

[GMC The Macro Man]

Another option

=SUMPRODUCT(($B$2:$B$100=$K$2)*(RIGHT($C$1:$H$1)="1")*($C$2:$H$100=N1))

Hi Fluff
I'm not getting the RIGHT function to work and that's probably because I changed the Column Headers to something more simplistic for this exercise but not realizing it would have an effect on the outcome.
The attachment details the correct headers for C, E, & G. Would this change the formula?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Name	Class	HWBAL1	HWB1	T&LAL1	T&L1	RAL1	R1						1	2	3	M
2	Aidan	P1	1	8	M	6	1	W		CLASS	P3		COUNT	3	4	5	3
3	Amelia	P2	2	B	M	5	1	S
4	Andrew	P3	3	C	3	9	1
5	Ava	P4	M	8	2	H	2	4
6	Clara	P1	1	7	1	U	2			K2 = CHOOSE CLASS
7	Dillon	P2	2	1A	3		3	4		N2:Q2 =COUNT COLUMNS C,E and G
8	Emily	P3	3		M		1
9	Freddie	P4	M		2		3
10	Georgie	P1	M	3	M	2	2	7
11	Jacob	P2	2	5	M		2
12	Kaiden	P3	3	T	1	1	M	3
13	Kayla	P4	M		1		M	A
14	Layla	P1	1		1	4	1	A
15	Lena	P2	3	8	2		1
16	Matthew	P3	2	8	2	G	2	M
17	Millie	P4	M		M	T	1
18	Quinn	P1	1	B	3		M	E
19	Ruaridh	P2	2	C	M	J	3	E
20	Sam	P3	M		2		3	3
21	Saoirse	P4	M		2	P	3
22
Sheet1

 

[etaf]

looks like AL1 is unique to those 3 columns
maybe
=SUMPRODUCT(($B$2:$B$100=$K$2)*(RIGHT($C$1:$H$1,3)="AL1")*($C$2:$H$100=N1))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Name	Class	HWBAL1	HWB1	T&LAL1	T&L1	RAL1	R1						1	2	3	M
2	Aidan	P1	1	8	M	6	1	W		CLASS	P3		COUNT	3	4	5	3
3	Amelia	P2	2	B	M	5	1	S						3	4	5	3
4	Andrew	P3	3	C	3	9	1
5	Ava	P4	M	8	2	H	2	4
6	Clara	P1	1	7	1	U	2			K2 = CHOOSE CLASS
7	Dillon	P2	2	1A	3		3	4		N2:Q2 =COUNT COLUMNS C,E and G
8	Emily	P3	3		M		1
9	Freddie	P4	M		2		3
10	Georgie	P1	M	3	M	2	2	7
11	Jacob	P2	2	5	M		2
12	Kaiden	P3	3	T	1	1	M	3
13	Kayla	P4	M		1		M	A
14	Layla	P1	1		1	4	1	A
15	Lena	P2	3	8	2		1
16	Matthew	P3	2	8	2	G	2	M
17	Millie	P4	M		M	T	1
18	Quinn	P1	1	B	3		M	E
19	Ruaridh	P2	2	C	M	J	3	E
20	Sam	P3	M		2		3	3
21	Saoirse	P4	M		2	P	3
22
23
Sheet1

Cell Formulas
Range	Formula
N3:Q3	N3	=SUMPRODUCT(($B$2:$B$100=$K$2)*(RIGHT($C$1:$H$1,3)="AL1")*($C$2:$H$100=N1))

 

[GMC The Macro Man]

looks like AL1 is unique to those 3 columns
maybe
=SUMPRODUCT(($B$2:$B$100=$K$2)*(RIGHT($C$1:$H$1,3)="AL1")*($C$2:$H$100=N1))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Name	Class	HWBAL1	HWB1	T&LAL1	T&L1	RAL1	R1						1	2	3	M
2	Aidan	P1	1	8	M	6	1	W		CLASS	P3		COUNT	3	4	5	3
3	Amelia	P2	2	B	M	5	1	S						3	4	5	3
4	Andrew	P3	3	C	3	9	1
5	Ava	P4	M	8	2	H	2	4
6	Clara	P1	1	7	1	U	2			K2 = CHOOSE CLASS
7	Dillon	P2	2	1A	3		3	4		N2:Q2 =COUNT COLUMNS C,E and G
8	Emily	P3	3		M		1
9	Freddie	P4	M		2		3
10	Georgie	P1	M	3	M	2	2	7
11	Jacob	P2	2	5	M		2
12	Kaiden	P3	3	T	1	1	M	3
13	Kayla	P4	M		1		M	A
14	Layla	P1	1		1	4	1	A
15	Lena	P2	3	8	2		1
16	Matthew	P3	2	8	2	G	2	M
17	Millie	P4	M		M	T	1
18	Quinn	P1	1	B	3		M	E
19	Ruaridh	P2	2	C	M	J	3	E
20	Sam	P3	M		2		3	3
21	Saoirse	P4	M		2	P	3
22
23
Sheet1

Cell Formulas
Range	Formula
N3:Q3	N3	=SUMPRODUCT(($B$2:$B$100=$K$2)*(RIGHT($C$1:$H$1,3)="AL1")*($C$2:$H$100=N1))

Hi etaf
Yes that is correct, the AL1 are unique to these 3 columns and when i followed the formula above, it worked great except for "Q2" as the value here is a letter (M) which needs the number of instances counted.
Is there a different formula to count letters?

 

[etaf]

should work
=SUMPRODUCT(($B$2:$B$100=$K$2)*(RIGHT($C$1:$H$1,3)="AL1")*($C$2:$H$100=N$1))
maybe a $ infromt of 1 , in case you are copying down at all
also
is there a space before or after the M
as you can see it worked in my example and Fluff posted

 

[GMC The Macro Man]

Spot on etaf, there was a space before the M. I should have seen that
Thanks to you and Fluff for your help
GMC

 

[etaf]

you are welcome