Counting hidden numerical value to individual words

[ANTHILL]

Greetings to you all.

So I have a row of cells that have questions and their respective answers that contain a "yes" or "no" response.

I would like to assign a 'hidden' numerical value to each individual "yes" response. Then I would like to add up the total.

I am a beginner to Excel so would rather use formulas rather than VBA!

Any assistance would be greatly appreciated.

Many thanks.

Ant

 

[Joe4]

Will each "Yes" have the same value, or different values for different questions?
Can you post a simple example?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Marcelo Branco]

See if this example helps

Suppose, for example, the points for "Yes" on Question1, Question2,..., Question5 are, respectively, 3, 2, 1, 4, 4.

Then go to Formulas > Names Manager and add a Name
New
Name: QuestionPoints
Refers to: ={3,2,1,4,4}

And try this

Pasta1
	A	B	C	D	E	F	G
1	Name	Question1	Question2	Question3	Question4	Question5	Total Points
2	John	No	Yes	Yes	No	Yes	7
3	Mike	Yes	Yes	No	No	Yes	9
4	Mary	Yes	No	No	Yes	Yes	11
Plan4

Cell Formulas
Range	Formula
G2:G4	G2	=SUMPRODUCT(--(B2:F2="Yes"),QuestionPoints)

M.

 

[ANTHILL]

See if this example helps

Suppose, for example, the points for "Yes" on Question1, Question2,..., Question5 are, respectively, 3, 2, 1, 4, 4.

Then go to Formulas > Names Manager and add a Name
New
Name: QuestionPoints
Refers to: ={3,2,1,4,4}

And try this

Pasta1
	A	B	C	D	E	F	G
1	Name	Question1	Question2	Question3	Question4	Question5	Total Points
2	John	No	Yes	Yes	No	Yes	7
3	Mike	Yes	Yes	No	No	Yes	9
4	Mary	Yes	No	No	Yes	Yes	11
Plan4

Cell Formulas
Range	Formula
G2:G4	G2	=SUMPRODUCT(--(B2:F2="Yes"),QuestionPoints)

M.

Many thanks Marcelo. Regards. Ant

 

[ANTHILL]

Will each "Yes" have the same value, or different values for different questions?
Can you post a simple example?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks Joe, sorry about the delayed response- I'm GMT plus three. The reply below has helped. Cheers. Ant