Highest value and total value

[apgmin]

Hello All,
Can any one please help me with this.

Column J has the list of all students names

In Column B we have to select the students name as it is data validation, list of J1 to J1000
Column C ( course name ) is the V look up so it gets auto populated
column D we mention the semester number for which he has paid the fees ( Value 1-6 in numerical)

column I to N has the heading Sem 1, Sem 2 and so on till Sem 6
In these columns adjacent to the student name I should get the value from the amount column E
Like when I fill the student E the first time on 1st Jan and mention in sem column as 1 the sem 1 column shows 10 against e
then again when I fill the student E the second time on 3rd Aug and mention in sem column as 2 the sem 2 column shows 15 against e
So on when I fill the student E the next time on 5th sept and mention in sem column as 3 the sem 3 column shows 12 against e

date

student name

COURSE NAME

sem

amount

MASTER LIST

COURSE NAME

sem 1

sem 2

sem 3

sem 4

sem 5

sem 6

01-Jan

a

BA

1

10

a

BA

10

01-Jan

b

MA

1

20

b

MA

10

01-Jan

c

MA

1

20

c

MA

10

15

01-Jan

d

BA

1

10

d

BA

10

01-Jan

e

BA

1

10

e

BA

10

15

12

Jan-18

f

BA

1

10

f

BA

10

15

01-Jan

g

BA

1

10

g

BA

10

15

01-Jan

h

MA

1

10

h

MA

10

03-Aug

c

MA

2

15

03-Aug

f

BA

2

15

03-Aug

e

BA

2

15

03-Aug

g

BA

2

15

05-Sep

e

BA

3

12

05-Sep

b

MA

2

15

05-Sep

d

BA

2

15

<tbody>
</tbody>

 

[JustynaMK]

Hi apgmin,

Assuming that your table is organised as follows:
- headers in row 1
- "date" to "amount" fields present in columns A-E
- "MASTER LIST" to "sem 6" fields present in column G-N

Then you can try using the following formula in cell I2 (drag it down & across to see the remaining values):
=SUMIFS($E:$E,$B:$B,$G2,$C:$C,$H2,$D:$D,RIGHT(I$1,1))

Please note that the final values are slightly different to what you presented in your post (e.g. results for student "b"), but I will let you revise it.
Let me know if that helps.