<P ID="edit" class=small>(Edited by jscher2000 on 19-Aug-05 18:27. )</P><font color=red>Sorry, never mind. I realized reading my own post that I was inserting a string and needed to use VALUE() around the RIGHT() function.</font color=red>

I have a workbook with two pages. One contains the answers students gave to multiple choice questions (e.g., A, B, C, D, E or F), plus any partial credit if the wrong answer was explained well. The other page contains formuale that calculate the credit (5 for correct, partial credit for incorrect if any) the student receives for that question It also contains 4 columns of numeric scores for essay questions.

Using SUM across the columns on the second sheet works fine. However, SUMIF ignores the cells that contain the IF() formulae. These look like this:

The "multiple choice score" cell (in Scores_C6):

=IF(MC_Answers!C6=MC_Answers!C$4,C$5,IF(LEN(MC_Ans wers!C6)>1,RIGHT(MC_Answers!C6,1),0))

(MC_Answers!C$4 holds the correct letter response, and Scores_C$5 holds the point value for a correct answer)

The "sum of partial credits" cell:


If the essay score was <5, it was partial credit, so those are summed. But not the multiple choices under 5. Perhaps this can be fixed by adding a sheet, but if there some quick fix for the existing layout I would be very happy.