Sum not summing

[leejones15]

I've attached the file in question. It is the results from a survey and I converted the results of some of the questions from a Likert scale to variables. The column "Post Test" at the bottom (I colored it red) is supposed to sum up all the scores, but it only sums some of them. No rhyme or reason that I can find for which ones it adds. All cells have been formatted to "numbers", so that isn't the issue.
Any other ideas? Thanks

[Jonmo1]

Remove the quote marks you have around the 5 in the formulas in S21 to AT32

=IF(S3="Disagree",1,IF(S3="Neutral",2,IF(S3="Agree",3,IF(S3="Strongly disagree",0,"5"))))

"5" is NOT a number, it's a TEXT string, therefore it is not summed.
change that to just 5

[leejones15]

Remove the quote marks you have around the 5 in the formulas in S21 to AT32

=IF(S3="Disagree",1,IF(S3="Neutral",2,IF(S3="Agree",3,IF(S3="Strongly disagree",0,"5"))))

"5" is NOT a number, it's a TEXT string, therefore it is not summed.
change that to just 5

That was it. Thanks!

[Jonmo1]

You're welcome.

[oeldere]

Use VLookup to find the values. (see the bleu cells for the VLookup table).

The formula are in the green cells (vlookup).

After that you can sum the data.

See the attached file.