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?

```=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
``````=index(if(A2:A="",,mmult(--switch(B2:I,"Good",2,"Mediocre",1,),sequence(columns(B2:I))^0)))