Sum values from arguments registered in cells

[quemuenchatocha]

Good day

I need some help. I am trying to calculate the cumulative grades obtained by each of the students (5), in each of the subjects taken (9). For example if I select student Abby (D17) from a drop down list and I want to know her cumulative grade recorded in chemistry (D18), for cell D20 I should get the total which would correspond to 11. However, I have tried different combinations of INDEX+MATCH, OFFSET and SUMIFS, and none of them has given me results, I think I am failing in some calculation.

Bakery Shopping List - STARTER.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2
3	Name	Mathematics	Physics	Chemistry	Sports	Algebra	History	Geography	French	Computers
4	Abby	2	3	4	5	3	3	4	2	2
5	Bill	5	5	1	5	4	3	3	2	5
6	Cathy	3	4	2	5	3	4	2	4	4
7	Derek	4	3	3	5	4	5	1	5	5
8	Bill	5	2	3	5	3	5	2	4	1
9	Abby	4	2	4	5	5	4	2	4	2
10	Emily	2	1	4	5	2	2	2	2	3
11	Abby	3	3	3	5	1	1	3	3	3
12	Derek	0	4	2	5	3	2	2	1	1
13	Cathy	3	5	1	5	3	1	3	2	2
14		31	32	27	50	31	30	24	29	28
15
16
17			Name:	Abby
18			Subject	Chemistry
19
20			Total:
21
Original data

Cell Formulas
Range	Formula
B14	B14	=SUBTOTAL(109,[Mathematics])
C14	C14	=SUBTOTAL(109,[Physics])
D14	D14	=SUBTOTAL(109,[Chemistry])
E14	E14	=SUBTOTAL(109,[Sports])
F14	F14	=SUBTOTAL(109,[Algebra])
G14	G14	=SUBTOTAL(109,[History])
H14	H14	=SUBTOTAL(109,[Geography])
I14	I14	=SUBTOTAL(109,[French])
J14	J14	=SUBTOTAL(109,[[Computers ]])

Cells with Data Validation
Cell	Allow	Criteria
D17	List	=$M$3:$M$7
D18	List	=$B$3:$J$3

Could you please give me a hint about the best way to perform this calculation, I can not add up the cumulative grades of each of the students obtained in each of their subjects taken.

Thank you very much for your attention.

PS. I am using Excel 365 version

 

[DanteAmor]

How about:

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1
2
3	Name	Mathematics	Physics	Chemistry	Sports	Algebra	History	Geography	French	Computers
4	Abby	2	3	4	5	3	3	4	2	2
5	Bill	5	5	1	5	4	3	3	2	5
6	Cathy	3	4	2	5	3	4	2	4	4
7	Derek	4	3	3	5	4	5	1	5	5
8	Bill	5	2	3	5	3	5	2	4	1
9	Abby	4	2	4	5	5	4	2	4	2
10	Emily	2	1	4	5	2	2	2	2	3
11	Abby	3	3	3	5	1	1	3	3	3
12	Derek	0	4	2	5	3	2	2	1	1
13	Cathy	3	5	1	5	3	1	3	2	2
14		31	32	27	50	31	30	24	29	28
15
16
17			Name:	Abby
18			Subject	Chemistry
19
20			Total:	11
Hoja6

Cell Formulas
Range	Formula
D20	D20	=SUMPRODUCT((A4:A13=D17)*(Table1[[#Headers],[Mathematics]:[Computers ]]=D18)*(B4:J13))

 

[DanteAmor]

Or if you want all the sums of all the students you can do it with a pivot table:

 

[Peter_SSs]

Another option.

quemuenchatocha.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2
3	Name	Mathematics	Physics	Chemistry	Sports	Algebra	History	Geography	French	Computers
4	Abby	2	3	4	5	3	3	4	2	2
5	Bill	5	5	1	5	4	3	3	2	5
6	Cathy	3	4	2	5	3	4	2	4	4
7	Derek	4	3	3	5	4	5	1	5	5
8	Bill	5	2	3	5	3	5	2	4	1
9	Abby	4	2	4	5	5	4	2	4	2
10	Emily	2	1	4	5	2	2	2	2	3
11	Abby	3	3	3	5	1	1	3	3	3
12	Derek	0	4	2	5	3	2	2	1	1
13	Cathy	3	5	1	5	3	1	3	2	2
14	Total	31	32	27	50	31	30	24	29	28
15
16
17			Name:	Abby
18			Subject	Chemistry
19
20			Total:	11
Sheet1

Cell Formulas
Range	Formula
B14	B14	=SUBTOTAL(109,[Mathematics])
C14	C14	=SUBTOTAL(109,[Physics])
D14	D14	=SUBTOTAL(109,[Chemistry])
E14	E14	=SUBTOTAL(109,[Sports])
F14	F14	=SUBTOTAL(109,[Algebra])
G14	G14	=SUBTOTAL(109,[History])
H14	H14	=SUBTOTAL(109,[Geography])
I14	I14	=SUBTOTAL(109,[French])
J14	J14	=SUBTOTAL(109,[[Computers ]])
D20	D20	=SUMIF(Table1[Name],D17,INDIRECT("Table1["&D18&"]"))

 

[quemuenchatocha]

Dear @DanteAmor, thank you very much for your valuable collaboration, it has helped me a lot to solve the doubts I had about this procedure, a big hug!

 

[quemuenchatocha]

Dear @Peter_SSs thank you for your contribution, your alternative is valuable to me. I wanted to ask you if it is possible you can help me explaining me how the part of the function where you incorporate INDIRECT("Table1["&D18&"]") works, I am relatively new to these issues, I know how INDIRECT works but I am a little confused about the argument "Table1["&D18&"]", especially the part that goes inside [...]. Thanks again!

 

[DanteAmor]

Dear @DanteAmor, thank you very much for your valuable collaboration, it has helped me a lot to solve the doubts I had about this procedure, a big hug!

Glad we could help & thanks for the feedback

 

[Peter_SSs]

Dear @Peter_SSs thank you for your contribution, your alternative is valuable to me.

You are welcome.

I know how INDIRECT works but I am a little confused about the argument "Table1["&D18&"]", especially the part that goes inside [...].

Assuming that your table name is "Table1" then the syntax Table1[History] refers to the History column in the table (excluding the heading), Table1[Chemistry] refers to the Chemistry column etc.
By using INDIRECT here I am just feeding the subject from cell D18 into that syntax. So with the layout and values shown in post #4 my formula translates as follows

=SUMIF(Table1[Name],D17,INDIRECT("Table1["&D18&"]"))
becomes
=SUMIF(Table1[Name],D17,Table1[Chemistry])
=SUMIF(A4:A13,"Abby",D4:D13)
= 11

 

[quemuenchatocha]

You are welcome.

Dear @Peter_SSs, Thank you very much for your valuable information, now it is clearer to me how the formula works, very grateful, big hug!

 

[Peter_SSs]

No problem. Glad to help.