Drop Down Selection Formula

hi, i'm having a problem with a worksheet from work.

it lists various questions for scoring purposes, that must be answered from a drop down menu beside it with either "yes", "no", or "n/a".

the problem i am having is that yes and no are set to different point values & the total must be calculated at the bottom of the page in a specific cell.
ie. question 1 point value is 5, yes would give u 5, no would minus 5 & n/a would be null.

i have a bout 40 questions but i am unsure of which formula to use, as vlookup didnt work out too well.

Are the points the same for all questions? If so then if your 40 answers are in B2:B41 then use this formula to calculate total score

=SUM(COUNTIF(B2:B41,{"Yes","No"})*{5,-5})

thank you for a quick answer but unfortunately, no. some questions have lower values or higher values.

how would i go about doing that?

Well assume B2:B41 has answers as before and C2:C41 contains the score for each question, e.g. if C2 contains 4 this means a "Yes" in B2 scores 4, a "No" in B2 scores -4, then use formula

=SUM(SUMIF(B2:B41,{"Yes","No"},C2:C41)*{1,-1})

You can hide column C or put the scores in another sheet....