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

[baidya91]

[TABLE="width: 1026"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]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?



















[/TD]
[TD]51[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]45[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]63[/TD]
[TD]B+[/TD]
[TD][/TD]
[TD][/TD]
[TD]46[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]52[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]59[/TD]
[TD]F[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Score1	Grade1	Score2	Grade2	Score3	Grade3	Score4	Grade4	Score5	Grade5	Score6	Grade6
B	C	B+	C	B	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]51[/TD]

[TD="align: right"]45[/TD]

[TD="align: right"]63[/TD]

[TD="align: right"]46[/TD]

[TD="align: right"]52[/TD]

[TD="align: right"]59[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]257[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A4[/TH]
[TD="align: left"]{=SUM(LARGE(IF(MOD(COLUMN(B2:L2),2)=0,IF(B2:L2<>"F",A2:K2)),{1,2,3,4,5}))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Score1	Grade1	Score2	Grade2	Score3	Grade3	Score4	Grade4	Score5	Grade5	Score6	Grade6
B	C	B+	C	B	F

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]51[/TD]

[TD="align: right"]45[/TD]

[TD="align: right"]63[/TD]

[TD="align: right"]46[/TD]

[TD="align: right"]52[/TD]

[TD="align: right"]59[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]257[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

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

Thanks in advance, Sir.


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

[TABLE="width: 770"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]48[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]51[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]77[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD]45[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]78[/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]60[/TD]
[TD]B+[/TD]
[/TR]
</tbody>[/TABLE]


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":

[TABLE="width: 770"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]48[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]51[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]77[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD]45[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]78[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]60[/TD]
[TD]B+[/TD]
[/TR]
</tbody>[/TABLE]


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}))


[TABLE="width: 907"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Score[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]Grade[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]51[/TD]
[TD]B[/TD]
[TD]45[/TD]
[TD]C[/TD]
[TD]63[/TD]
[TD]B+[/TD]
[TD]46[/TD]
[TD]C[/TD]
[TD]52[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]54[/TD]
[TD]F[/TD]
[TD]66[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]278[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[TABLE="width: 748"]
<tbody>[TR]
[TD][/TD]
[TD][TABLE="width: 770"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]48[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]51[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]77[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD]45[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]78[/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]60[/TD]
[TD]B+[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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}))


[TABLE="width: 907"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Score[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]Grade[/TD]
[TD]Score[/TD]
[TD]Grade[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]51[/TD]
[TD]B[/TD]
[TD]45[/TD]
[TD]C[/TD]
[TD]63[/TD]
[TD]B+[/TD]
[TD]46[/TD]
[TD]C[/TD]
[TD]52[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]54[/TD]
[TD]F[/TD]
[TD]66[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]278[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 766"]
<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>[TR]
[TD][/TD]
[TD][/TD]
[TD]51[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]45[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]63[/TD]
[TD]B+[/TD]
[TD][/TD]
[TD][/TD]
[TD]46[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]52[/TD]
[TD]B[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]59[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]61[/TD]
[TD]B+[/TD]
[TD][/TD]
[TD][/TD]
[TD]48[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]64[/TD]
[TD]B+[/TD]
[TD][/TD]
[TD][/TD]
[TD]61[/TD]
[TD]B+[/TD]
[TD][/TD]
[TD][/TD]
[TD]66[/TD]
[TD]B+[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]55[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]48[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]51[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]77[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD]45[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD]78[/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD]60[/TD]
[TD]B+[/TD]
[/TR]
</tbody>[/TABLE]

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}))

[TABLE="width: 649"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="4"><col><col></colgroup><tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]51[/TD]
[TD]B[/TD]
[TD]45[/TD]
[TD]C[/TD]
[TD]63[/TD]
[TD]B+[/TD]
[TD]46[/TD]
[TD]C[/TD]
[TD]52[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]59[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]61[/TD]
[TD]B+[/TD]
[TD]48[/TD]
[TD]C[/TD]
[TD]64[/TD]
[TD]B+[/TD]
[TD]61[/TD]
[TD]B+[/TD]
[TD]66[/TD]
[TD]B+[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]55[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]48[/TD]
[TD]C[/TD]
[TD]51[/TD]
[TD]B[/TD]
[TD]77[/TD]
[TD]A[/TD]
[TD]45[/TD]
[TD]C[/TD]
[TD]78[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]60[/TD]
[TD]B+[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]257[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]307[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]314[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 649"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="4"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]