Help with a (simple?) formula for a marking system?

Hiya

I'm not great with excel, but I thought this would be pretty simple (apparently not for me). I have a marking system whereby column A is the question number, B, C and D are criteria, and E is a final score. Essentially, each question can have a certain number of issues wrong in each criteria (maximum of 3 per criteria), and dependant on how many is wrong it will add a score to D. I'm writing my formula so that if I put a '1', '2' or '3' in the criteria columns, it will allocate a certain point (ie. 2 things wrong will get a higher point). Its possible to have numbers in just one or all three criteria columns.

The most simplest of questions can only get a maximum of 1 thing wrong per criteria, and so my formula looks like this:

=IF(B12=1,2)+(IF(C12=1,1)+(IF(D12=1,1)))

This formula works fine, and allocates the points into E depending on what I type in, so a maximum of 4 points can be reached. As an example question, this would be 'What is your name?'. You can only get your name wrong once, but in three different ways (forgetting to write it completely, writing down someone elses name, or spelling your name wrong).

However, some of the questions are more complex and have multiple answers, and so I'm trying to put in that, if for example, one criteria has 2 things wrong on it it will get more points than if it only has 1. I've typed my formula like this:

=IF(B11=1,10,IF(B11=2,11,IF(B11=3,12)+(IF(C11=1,10,IF(C11=2,11,IF(C11=3,12)+(IF(D11=1,3)))))))

This would be for a question like 'How many address have you lived at?'. If you've lived at 3 addresses, you can get this question wrong 3 times and in 3 different ways. So for example, if you forget to write down one address you lose 10 points, 2 addresses you lose 11 and so on. If you write down three addresses but none of them are your own, you lose 12 points - and if you spell any of them wrong you'll get 3 points on top of that.

If I type in a single criteria column it works fine, but if I type in more than one it won't add up the totals and it appears that column B seems to override anything else (so if I put a 2 in C, the score will be 11, but if I then also add a 1 to B, it will change the score to 10 instead of making it 21).

I don't know if any of this makes sense, I'm awful at explaining myself.. but what have I done wrong?

EDIT TO INCLUDE THIS:
Same question at another forum:
http://www.mrexcel.com/forum/excel-q...ng-system.html

Hi and welcome to the forum!

Perhaps it would clarify things if you could upload an actual workbook with several examples together with your desired results.

The FAQ explains how to attach a workbook.

Regards

Also, if you could kindly re-read the Forum Rules, in particular re Cross-Posting.

http://www.mrexcel.com/forum/excel-q...ng-system.html

Regards

This is a moderated forum. Please take a moment to read the forum rules (at the Forum Rules link, above).
As has been mentioned, if you post the same question to multiple forums, you must post links to the other forums.
I'll do that for you THIS time.

Perhaps this set up can be used:
A1:H1 contains these headings:

Please Login or Register  to view this content.

The Crit fields contain the base number for each criteria. Any Col_B through Col_D values will be added to the base.
Examples:

Please Login or Register  to view this content.

The score is calculated using this formula, copied down:

Please Login or Register  to view this content.

With that example, these are the results:

Please Login or Register  to view this content.

Is that something you can work with?