Sum and rank with condition

[Guest]

hi, I need help on this formula. E.G

Students Eng Science Maths Total Rank
A 50 60 40 =B1+C1+D1
B 60 35 x ???

My question:
1) When I copy the first formula (E1) to the rest cell, it calculate for E2. How to skip or blank that cell for any students if absent (x) for any subject?
2) Then I have to rank them according highest score. If any cells on column E blank or empty, the symbol #NA return. Any way to hide or blank the cell.

Thanks .

 

[JE McGimpsey]

1) One way:

=IF(COUNT(B2:C2)=3,SUM(B2:C2),"")

2) This will blank the cell

=IF(E2<>"",RANK(E2,$E$2:$E$9),"")

Alternatively, this will give the student or students with blanks the
bottom-most ranking:

=IF(E2<>"",RANK(E2,$E$2:$E$9),COUNT($E$2:$E$9)+1)

 

[Domenic]

Hi,

try,

Total =IF(OR(B1="x",C1="x",D1="x"),"",B1+C1+D1), and copy down

Rank =IF(E1="","",RANK(E1,E:E)), and copy down

Hope this helps!

 

[Guest]

hi,thanks a lot for your help.
Now I need show the summary of score (grades in column J, e.g 1C 1B 1D
Using Vlookup formula I manage to identify the grade.

Col A B C D E F G H I J
Students Eng Grade Science Grade Maths Grade Total Rank Score
A 50 C 60 B 40 D