How to calculate aggregate

[dalbee]

In creating an excel app to calculate the aggregate for students in their exams as used by WAEC to determine the student aggregate in BECE . The Excel has to select Six Subjects from ine Subjects, with Four being compulsory and Two other best I want an excel formular that will add the grade value of four compulsory subjects : English, Maths, Science, Social. And then automatically select 2 other subjects from the rest with best grade value.

For Instance, If a student has the following result:
1. English - 2
2. Maths - 1
3. Science - 1
4. Social - 2
5. ICT - 3
6. BDT - 3
7. RME - 1
8. French - 2
9. Twi -

I want a formular for this calculation :
English(2)+Maths(1)+Science(1)+Social(2) and randomly select best other two subjects from the rest : RME(1)+French(2) = 10

Kindly help me.

 

[Peter_SSs]

Welcome to the MrExcel board!

Are the 4 compulsory subjects always listed first like your example?

23 01 01.xlsm
	A	B	C	D
1	Subject	Grade		Aggregate
2	English	2		9
3	Maths	1
4	Science	1
5	Social	2
6	ICT	3
7	BDT	3
8	RME	1
9	French	2
10	Twi
Aggregate

Cell Formulas
Range	Formula
D2	D2	=SUM(B2:B5,AGGREGATE(15,6,B6:B10,{1,2}))
Press CTRL+SHIFT+ENTER to enter array formulas.

If the order is less structured ..

23 01 01.xlsm
	F	G	H	I
1	Subject	Grade		Aggregate
2	BDT	3		9
3	Maths	1
4	Science	1
5	Social	2
6	ICT	3
7	English	2
8	RME	1
9	French	2
10	Twi
Score

Cell Formulas
Range	Formula
I2	I2	=SUM(SUMIF(F2:F10,{"English","Maths","Science","Social"},G2:G10))+SUM(AGGREGATE(15,6,G2:G10/((G2:G10<>"")*ISNA(MATCH(F2:F10,{"English","Maths","Science","Social"},0))),{1,2}))
Press CTRL+SHIFT+ENTER to enter array formulas.

In each case note that for your Excel version you will need to confirm the formula with Ctrl+Shift+Enter, not just Enter