Yes's and No's to 10's and 0's

[bloo7997]

I have a column of data that has the answers "Yes" and "No" to a survey question. Im trying to sum the yes's and no's but yes should be equal to 10 and no should be equal to 0. How do I buld a formula that puts that equality in place?

[Ron Coderre]

Try something like this regular formula....
=COUNTIF(A1:A10,"yes")*10+COUNTIF(A1:A10,"no")
or this one...
=SUM(COUNTIF(A1:A10,{"yes","no"})*{10,1})

is that something you can work with?

[Cutter]

Why would you sum "no" if "no" is worth 0. Wouldn't it just be:
=COUNTIF(A1:A10,"yes")*10

[bloo7997]

So this column is part of a worksheet with several columns of survey answers, the remainder of which are numbers...this is the only yes no column. The larger theme I'm working with is a sumproduct that pulls the numbers for a specific employee for all their surveys and retuns the total to a different cell/sheet where it is then divided by number of surveys. I dont necessarily need it to add 0 for no but would like to make them 0 to preserve continuity with all responces being numbers. I tried using an if formula as an array in a different column and got my 10' and 0's but when I plugged that column into my sumproduct formula it gave me #value and I havent been able to figure out the formatting discrepancy to make it work.

[Ron Coderre]

Why would you sum "no" if "no" is worth 0. Wouldn't it just be:
=COUNTIF(A1:A10,"yes")*10

Hmmm...totally missed the: No=0 part, so you're right.
I just assumed the NO answers would count for something.

[Cutter]

I just assumed the NO answers would count for something.

Only in the context of "No means No".

[bloo7997]

So if i need to count yes in that column for a specific employee found in a different column (emp are in N and Y/N is in X) what would the formula be. Im looking for something like if n2:n870=*employee* then count # of yes entries in x2:x870 for that employee....

[Ron Coderre]

So if i need to count yes in that column for a specific employee found in a different column (emp are in N and Y/N is in X) what would the formula be. Im looking for something like if n2:n870=*employee* then count # of yes entries in x2:x870 for that employee....

In order to avoid a cycle of "Oh, one more thing"...Are there any other sleeper requirements that might pop up as we go along? We can offer better help when we know all of the circumstances.

[Pepe Le Mokko]

...Are there any other sleeper requirements ...

Nice expression !

[bloo7997]

I wasnt trying to make anything appear to be a "sleeper" requirement, the problem Im having relates to the yes/no issue and I figured I could deduce the rest as I've got the formula working for the remainder of the data. As i said in a previous post I've been able to use an array formula to change them to 10 and 0 in a different column but it wont sum them because of some format issue I havent been able to figure out (when I click on function help it shows them as #VALUE instead of numbers)...and I was thinking if there were some way to write a formula that just counts them and roll it into something similar to what I used on the rest of the sheet that would be good, but I'm obviously still learning and havent been able to figure that out either. I dont have the sheet with me as it is on my work computer but the formula Im using to pull the remainder of the data is along the lines of:

=SUMPRODUCT((N2:N870=tech!Q4)*(~2:~870)) where ~ is the column containing the answers to the particular survey question I'm totaling and N is the employee column.

[Ron Coderre]

regarding...

=SUMPRODUCT((N2:N870=tech!Q4)*(~2:~870)) where ~ is the column containing the answers to the particular survey question I'm totaling and N is the employee column.

Try this...
=SUMPRODUCT((N2:N870=tech!Q4)*(~2:~870="Y"))

Does that help?

[bloo7997]

That worked grat! Thank you

[Ron Coderre]

That worked grat! Thank you

Glad I could help!