Get a % based on a category and the total amount of that category

[PresFox]

Hello there,

I am making an analysis tool for a test at the school I work at.

What I have:
A table containing the question numbers (1 to 20), the category each question belongs to (1 to 5), and a list of names. The teacher will add a 1 if a question is answered wrong.

What I want:
To make this sheet usable for other tests (wich have the same amount of questions, though the amount per category might change), I want excel to calculate a % based on the amount of questions that fall in a certain category and the total amount of them. Tried to work with HLOOKUP, but that does not seem to work. Always says 1.

This is the sheet I work with:

Any help would be greatly appreciated!

 

[etaf]

something like
not sure of the ranges you are using
=countif($B$2:$Z$2, Right(B200,1))
This will count all the questions that are in the cat - assuming Cat 1, cat 2 etc is in row200
Then you need to sumup the correct answers
BUT I dont know how that is shown - are they just blank cells ?
Again a SUMIFS() maybe , or maybe a SUMPRODUCT() to add all the 1's by pupil or add up the correct answers , not sure how they are shown - and subtract from the max per category
then to get a %
=SUMIFS( )/countif()

can you use XL2BB add-in - see signature / menu for how
OR
put a sample onto a share like dropbox/onedrive/google sheets

BUT how are correct question marked

 

[lrobbo314]

How about this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Category	1	1	2	2	1	3	3	3	4	4	5	4	4	1	1	3	3	4	4	5
2	Number	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
3	Pietje	1			1		1		1		1		1			1		1		1	1
4	Jan	1	1			1									1	1	1	1
5	Klaas
6
7		Categories
8	Analysis	1	2	3	4	5
9	Pietje	40%	50%	60%	50%	50%
10	Jan	100%	0%	40%	0%	0%
11	Klaas	0%	0%	0%	0%	0%
Sheet1

Cell Formulas
Range	Formula
B2:U2	B2	=SEQUENCE(,20)
B8:F8	B8	=SEQUENCE(,5)
B9:F11	B9	=LET(cat,$B$1:$U$1,nam,$A$3:$A$5,d,$B$3:$U$5,f,SUMPRODUCT((cat=B$8)*(nam=$A9)*d),t,SUMPRODUCT(--(cat=B$8)),f/t)
Dynamic array formulas.

 

[PresFox]

Thanks!

One question, got an error about names. What is "cat" and "nam" in this formula?

 

[lrobbo314]

I saw that you were on office 365 and assumed that you had the 'Let' function available.

Try this instead.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Category	1	1	2	2	1	3	3	3	4	4	5	4	4	1	1	3	3	4	4	5
2	Number	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20
3	Pietje	1			1		1		1		1		1			1		1		1	1
4	Jan	1	1			1									1	1	1	1
5	Klaas
6
7		Categories
8	Analysis	1	2	3	4	5
9	Pietje	40%	50%	60%	50%	50%
10	Jan	100%	0%	40%	0%	0%
11	Klaas	0%	0%	0%	0%	0%
Sheet1

Cell Formulas
Range	Formula
B2:U2	B2	=SEQUENCE(,20)
B8:F8	B8	=SEQUENCE(,5)
B9:F11	B9	=SUMPRODUCT(($B$1:$U$1=B$8)*($A$3:$A$5=$A9)*$B$3:$U$5)/SUMPRODUCT(--($B$1:$U$1=B$8))
Dynamic array formulas.