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 |
Array Formulas
<thead> </thead><tbody> </tbody> Note: Do not try and enter the {} manually yourself |
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 Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
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>
Note: Do not try and enter the {} manually yourself
<tbody>
</tbody>
48 | C | 51 | B | 77 | A | 45 | C | 78 | A | 60 | B+ |
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
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 |
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+ |
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 |