Sum values attributed to Yes, No, Maybe text answers

[skinnea]

I’ve a spreadsheet of 25 questions. Each q has a drop down answer cell. Values are Yes, No, Maybe - which are given the values 1, 0, 0.5 respectively.

Without using a helper column (to show those values) is there a formula that will sum each group of 5 answers in a single cell?

 

[Joe4]

Let's say that you had 5 answers between A1:A5, and you wanted to sum those results, based on your coniditon.
Then you could use a formula like this:

=COUNTIF(A1:A5,"Yes")+(COUNTIF(A1:A5,"Maybe")/2)

If that is not what you are looking for, then it isn't quite clear to me what you are asking. In that case, could you show us a simple example of your data and expected output?

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.

 

[Fluff]

Another option

=SUM(COUNTIFS(A1:A5,{"yes","maybe"})*{1,0.5})

 

[skinnea]

These both work brilliantly! Thank you

 

[Fluff]

Glad we could help & thanks for the feedback.