how to manage students' grades in excel

Question

how can i use excel to count how many A's, how many B's my students got in their exam?

column A is for students' name column B,D,F,H are for their marks for each subject column c,E,G,I are for their grades depending on the marks

so how can i make excel counts the grades that they got?

for example: if a student marks are as below :

john : mate A science C bio B Literature A

so i want excel to display "2A1B1C" in a cell. how can i do that? what formula should i use? thanks in advance

Answer 1

Use the following formula:
=IF(COUNTIF(B2:I2,"A")=0,"",COUNTIF(B2:I2,"A")&"A")&IF(COUNTIF(B2:I2,"B")=0,"",COUNTIF(B2:I2,"B")&"B")&IF(COUNTIF(B2:I2,"C")=0,"",COUNTIF(B2:I2,"C")&"C")
This formula tests if the count is 0 it will remove the letter, if the count is >0 it will write it.

If you don't mind to have 0A4B0C the formula is:
=COUNTIF(B2:I2,"A")&"A"&COUNTIF(B2:I2,"B")&"B"&COUNTIF(B2:I2,"C")&"C"