SUMIF Dynamic Range

[drunkendentist]

Hello there,

I'm attempting to use SUMIFs with dynamic rows or columns, here is a sample dataset

<tbody>
[TD="class: xl63"][/TD]
[TD="class: xl63, width: 51"]Score 1[/TD]
[TD="class: xl63, width: 51"]Score 2[/TD]
[TD="class: xl63, width: 51"]Score 3[/TD]
[TD="class: xl63, width: 51"]Score 4[/TD]
[TD="class: xl63, width: 51"]Score 5[/TD]

[TD="class: xl63"]Student 1[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]6[/TD]

[TD="class: xl63"]Student 2[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]3[/TD]

[TD="class: xl63"]Student 3[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]5[/TD]

[TD="class: xl63"]Student 4[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]5[/TD]

[TD="class: xl63"]Student 5[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]3[/TD]

[TD="class: xl63"]Student 6[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]6[/TD]

[TD="class: xl63"]Student 7[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]2[/TD]

[TD="class: xl63"]Student 8[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]

[TD="class: xl63"]Student 9[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]3[/TD]

[TD="class: xl63"]Student 10[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]0[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]5[/TD]

</tbody>

I want to...

=SUMIF(Row of student A4,$C1001:$GT1001,">= "))

In essence, the student names are in cells A2, A3, A4 etc (in a jumbled up order) and I would like to do some form of lookup or index match, that looks up the student name and then dynamically finds the row to so the sum of.

Hope that makes, many thanks for your time.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1		Score 1	Score 2	Score 3	Score 4	Score 5
2	Student 1	8	2	5	3	6		Student 10	18
3	Student 2	6	4	4	0	3		Student 3	17
4	Student 3	4	2	3	3	5
5	Student 4	7	2	2	3	5
6	Student 5	6	0	2	2	3
7	Student 6	6	0	5	3	6
8	Student 7	7	2	4	3	2
9	Student 8	5	2	0	5	5
10	Student 9	1	4	5	5	3
11	Student 10	5	0	3	5	5
Sheet1

Cell Formulas
Range	Formula
I2	=SUM(INDEX($B$2:$F$11,MATCH(H2,$A$2:$A$11,0),0))