Summing top 5 marks in excel excluding those marked 'F'

[baidya91]

Given below is the list of 6 marks with various grades. Now how can I sum top 5 marks excluding that marked "F" in excel?

51

B

45

C

63

B+

46

C

52

B

59

F

<tbody>
</tbody>

 

[Eric W]

You don't say how your sheet is laid out, but maybe something like this:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L
1	Score1	Grade1	Score2	Grade2	Score3	Grade3	Score4	Grade4	Score5	Grade5	Score6	Grade6
2	51	B	45	C	63	B+	46	C	52	B	59	F
3
4	257

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula A4 {=SUM(LARGE(IF(MOD(COLUMN(B2:L2),2)=0,IF(B2:L2<>"F",A2:K2)),{1,2,3,4,5}))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[baidya91]

You don't say how your sheet is laid out, but maybe something like this:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L
1	Score1	Grade1	Score2	Grade2	Score3	Grade3	Score4	Grade4	Score5	Grade5	Score6	Grade6
2	51	B	45	C	63	B+	46	C	52	B	59	F
3
4	257

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula A4 {=SUM(LARGE(IF(MOD(COLUMN(B2:L2),2)=0,IF(B2:L2<>"F",A2:K2)),{1,2,3,4,5}))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thanks in advance, Sir.


But this formula does not apply in case of the following where there is no grade as "F":

48

C

51

B

77

A

45

C

78

A

60

B+

<tbody>
</tbody>

Please find a solution

 

[PGD15]

Thanks in advance, Sir.


But this formula does not apply in case of the following where there is no grade as "F":

48

C

51

B

77

A

45

C

78

A

60

B+

<tbody>
</tbody>

Please find a solution

if you only get an error when there is no F present =iferror(SUM(LARGE(IF(MOD(COLUMN(B2:L2),2)=0,IF(B2:L2<>"F",A2:K2)),{1,2,3,4,5})),SUM(LARGE(IF(MOD(COLUMN(B2:L2),2)=0)

Hopefully that should work

 

[admiral100]

Hi,

Try this
Entered with Ctrl+Shift+Enter

A5 =SUM(LARGE(IFERROR(--(MOD(COLUMN(A2:R2),2) <> 0)*--(B2:S2 <> "F")*A2:R2,0),{1,2,3,4,5}))

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

Score

Grade

Score

Grade

Score

Grade

Score

Grade

Score

Grade

Score

Grade

Score

Grade

Score

Grade

Score

Grade

2

51

B

45

C

63

B+

46

C

52

B

54

F

66

C

3

4

5

278

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[baidya91]

No, sir that did not work. Formula says error. Please suggest some ways

 

[baidya91]

Please sir, i mean a formula that works both with grade "f" and without grade "f". But the one you have given does not apply in case of

48 C 51 B 77 A 45 C 78 A 60 B+ <tbody> </tbody>

<tbody>
</tbody>

 

[baidya91]

Please sir, find out a solution

 

[baidya91]

Hi,

Try this
Entered with Ctrl+Shift+Enter

A5 =SUM(LARGE(IFERROR(--(MOD(COLUMN(A2:R2),2) <> 0)*--(B2:S2 <> "F")*A2:R2,0),{1,2,3,4,5}))

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

Score

Grade

Score

Grade

Score

Grade

Score

Grade

Score

Grade

Score

Grade

Score

Grade

Score

Grade

Score

Grade

2

51

B

45

C

63

B+

46

C

52

B

54

F

66

C

3

4

5

278

<tbody>
</tbody>

		51	B			45	C			63	B+			46	C			52	B											59	F
		61	B+			48	C			64	B+			61	B+			66	B+											55	B
		48	C			51	B			77	A			45	C			78	A											60	B+

<colgroup><col><col><col><col><col><col><col span="2"><col><col><col><col><col><col><col><col><col span="2"><col><col><col span="2"><col span="2"><col span="4"><col span="2"><col span="2"></colgroup><tbody>
</tbody>

IN THE ABOVE THREE CASES YOUR FORMULA WORKS FINE WITH THE FIRST TWO CASES BUT IT DOES NOT WORK WITH THE THIRD ONE.

I HAVE TRIED BUT THE RESULT IT GIVES 330 IN PLACE OF 314. SO PLEASE SEE TO THAT.

 

[admiral100]

Hi,

Please note that the grade column is always in a even column and the score in odd column.

Entered with Ctrl+Shift+Enter and drag down

A6 =SUM(LARGE(IFERROR(--(MOD(COLUMN($A2:$P2),2) <> 0)*--($B2:$P2 <> "F")*$A2:$P2,0),{1,2,3,4,5}))

1	A	B	C	D	E	F	H	I	J	K	L	M	N	O	P	Q
2	51	B	45	C	63	B+	46	C	52	B					59	F
3	61	B+	48	C	64	B+	61	B+	66	B+					55	B
4	48	C	51	B	77	A	45	C	78	A					60	B+
5
6	257
7	307
8	314

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="4"><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="4"><col><col></colgroup><tbody>
</tbody>