How to assign weighted numeric values to text and sum?

[sccrsurfer]

Hi everyone. Hope you all had a great weekend. I'm stumped on a question that I hope someone can help me figure out.

I created a questionnaire via a user form, and when all the questions are answered, the text values are placed horizontally, on the same row in different columns. There answers are input in columns ranging E:P. What I want is to input a formula in column Q that assigns a weighted numeric value to the text value in each column and add those all together. By weighted, let's assume that each value in any given row through columns E:P will have a yes/no answer. I want a "yes" in one answer to be a 10, and "yes" in another to be worth a 5, etc...

Hopefully I've articulated my problem and desired solution clearly. Thanks in advance for everyone's help.

 

[tweedle]

This might be a start for you....

Excel Workbook
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Weight	5	5	5	5	5	5	10	10	10	10	10	10
3
4		Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	90
Sheet

 

[diddi]

if the survey data is collected on a userform, it may even be easier to maintain a tally at userform level and just save the result to your sheet

 

[Aladin Akyurek]

This might be a start for you....

Excel Workbook
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2	Weight	5	5	5	5	5	5	10	10	10	10	10	10
3
4		Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	Yes	90
Sheet

Why SumProduct? You have just 1 condition for doing a total. In such cases,
a SumIf formula is the fastest means...

=SUMIF(E4:P4,"Yes",E2:P2)

 

[tweedle]

True enough: Could be done within either the userform tally or as SumIf.
I'm not going to argue a few clock-cycles until I start crashing the platform, in which case I need to start looking at whether my solution is adequately scaled to the problem. < snip <SNIP other ramblings>

As with most things out here, 6 different ways to solution a challenge.