I would like to create a Google spreadsheet for grading students' homework. There are 8 rubrics (columns). In each rubric, there should be three levels: "good", "mediocre", "bad". They are worth, respectively, 2 points, 1 point and 0 points. The total grade is the sum of the grades in all 8 columns.

I found here an explanation of how to create the dropdown list for each rubric. I created a dropdown list that contains the three options: "good", "mediocre" or "bad" (see the example sheet). Now I need to write a formula for the final grade. One option is to create, for each column, an auxiliary column in which the text is converted to a number, e.g. using "If" or "Switch", and then sum the values in the 8 auxiliary columns. Is there a more efficient way to do this, without using extra columns?


2 Answers 2


you can get the same result with this calculation, extended to all student rows and easier to understand:

=if(A2:A = "" ,"",sum(switch(B2:I2,"Good",2,"Mediocre",1,0)))
  • Indeed it is much simpler, and I think I do not even need the first "IF" (I can simply copy this formula sum(switch(B2:I2,"Good",2,"Mediocre",1,0)) to all student rows). Jan 30, 2022 at 10:55
  • eliminating the IF, the extension of the calculation to one (or more) row with an empty student name would produce a sequence of useless zeros
    – Daniele
    Jan 30, 2022 at 11:01

Try this out in J2


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.