Sorry and help! Scoring items and then summing up total non-contiguous row values

[goodlivin72]

Hi All,
Please see attached spreadshseet which has 9 highlited non-contiguous variables which need to be 'scored' and then summed at the end of the row (Score??).

For each of ac1, ac2, ac3, etc. responses can range from 1 to 4 (although some are missing). I would like to be able to score total row sum using the the following criteria:

if answer=1 or 2, then score = 1
if answer=3, then score = -1
if answer=4, then score=0
if answer is missing (blank), then score= MISSING

Using this scoring protocol, scores can range from -9 to +9. As you will see in the attached spreadsheet, however, some persons are missing one or two scores, whereas for others persons all values are missing altogether!

Here is my problem. I want to be able to score each cell (see the 'ifs' above) and then sum across the non-contiguous columns to compute a final score WITHOUT counting the blanks as zeros. In other words, how can I add or SUM items up without Excel putting a darn 'zero' in the total row score where there is no data for the person?

Hope I've explained this okay and thanks in advance for your help.

ps- I've tried

[teylyn]

This is the third time you're asking the same question today. Read the forum rules.