formula with yes and no
 

I have a colum D3 thru D292 which has answer yes or no. I formated the cells
to custom "yes";"yes";"no".

I want to have a formula that counts the number of yes's and no's. Then
formula to show the percentage yes vers no answers of the total of 292
answers.

formula with yes and no
 

I formated the cells to custom "yes";"yes";"no".

I'm curious to know why you would do that?

That means if a positive or negative number is entered it displays as "yes"
and if a 0 is entered it displays as "no".

And if the TEXT entries "yes" or "no" are entered they aren't affected.

Are there TEXT entries "yes" and/or "no" in this range?

Perhaps you need to explain a little more.


--
Biff
Microsoft Excel MVP


"ksibly" wrote in message
...
I have a colum D3 thru D292 which has answer yes or no. I formated the
cells
to custom "yes";"yes";"no".

I want to have a formula that counts the number of yes's and no's. Then
formula to show the percentage yes vers no answers of the total of 292
answers.

formula with yes and no
 

for yes
=COUNTIF($D$3:$D$292,"yes")
for no
=COUNTIF($D$3:$D$292,"no")
for % yes
=COUNTIF($D$3:$D$292,"yes")/COUNTA($D$3:$D$292)
for % no
=COUNTIF($D$3:$D$292,"no")/COUNTA($D$3:$D$292)

Use countA so in case any cells are not filled in with yes or no or you have
blank cells in the range you will get the correct percentage. Dividing by
290 will give the wrong answer unless all the cells have yes or no.



"T. Valko" wrote:

I formated the cells to custom "yes";"yes";"no".

I'm curious to know why you would do that?

That means if a positive or negative number is entered it displays as "yes"
and if a 0 is entered it displays as "no".

And if the TEXT entries "yes" or "no" are entered they aren't affected.

Are there TEXT entries "yes" and/or "no" in this range?

Perhaps you need to explain a little more.


--
Biff
Microsoft Excel MVP


"ksibly" wrote in message
...
I have a colum D3 thru D292 which has answer yes or no. I formated the
cells
to custom "yes";"yes";"no".

I want to have a formula that counts the number of yes's and no's. Then
formula to show the percentage yes vers no answers of the total of 292
answers.

formula with yes and no
 

that will work if the OP is entering text "yes" and "no", however not if the
OP is entering numerical data and having it displayed as yes/no due to the
custom number format "yes";"yes";"no".

I think the OP needs to clarify.


"Joel" wrote:

for yes
=COUNTIF($D$3:$D$292,"yes")
for no
=COUNTIF($D$3:$D$292,"no")
for % yes
=COUNTIF($D$3:$D$292,"yes")/COUNTA($D$3:$D$292)
for % no
=COUNTIF($D$3:$D$292,"no")/COUNTA($D$3:$D$292)

Use countA so in case any cells are not filled in with yes or no or you have
blank cells in the range you will get the correct percentage. Dividing by
290 will give the wrong answer unless all the cells have yes or no.



"T. Valko" wrote:

I formated the cells to custom "yes";"yes";"no".

I'm curious to know why you would do that?

That means if a positive or negative number is entered it displays as "yes"
and if a 0 is entered it displays as "no".

And if the TEXT entries "yes" or "no" are entered they aren't affected.

Are there TEXT entries "yes" and/or "no" in this range?

Perhaps you need to explain a little more.


--
Biff
Microsoft Excel MVP


"ksibly" wrote in message
...
I have a colum D3 thru D292 which has answer yes or no. I formated the
cells
to custom "yes";"yes";"no".

I want to have a formula that counts the number of yes's and no's. Then
formula to show the percentage yes vers no answers of the total of 292
answers.

formula with yes and no
 

Thanks Joel. I did past the formulas in the cells below data. I.E
294,295,296 and 297 with your formulas but the results do not show. I did
change the format of the cells to text as you suggested. Did I do something
wrong? sorry new at this formula stuff.

"Joel" wrote:

for yes
=COUNTIF($D$3:$D$292,"yes")
for no
=COUNTIF($D$3:$D$292,"no")
for % yes
=COUNTIF($D$3:$D$292,"yes")/COUNTA($D$3:$D$292)
for % no
=COUNTIF($D$3:$D$292,"no")/COUNTA($D$3:$D$292)

Use countA so in case any cells are not filled in with yes or no or you have
blank cells in the range you will get the correct percentage. Dividing by
290 will give the wrong answer unless all the cells have yes or no.



"T. Valko" wrote:

I formated the cells to custom "yes";"yes";"no".

I'm curious to know why you would do that?

That means if a positive or negative number is entered it displays as "yes"
and if a 0 is entered it displays as "no".

And if the TEXT entries "yes" or "no" are entered they aren't affected.

Are there TEXT entries "yes" and/or "no" in this range?

Perhaps you need to explain a little more.


--
Biff
Microsoft Excel MVP


"ksibly" wrote in message
...
I have a colum D3 thru D292 which has answer yes or no. I formated the
cells
to custom "yes";"yes";"no".

I want to have a formula that counts the number of yes's and no's. Then
formula to show the percentage yes vers no answers of the total of 292
answers.

formula with yes and no
 

New at this thought to get the correct number would need to change the cell
format. Should I have left it a general or Text?

"T. Valko" wrote:

I formated the cells to custom "yes";"yes";"no".

I'm curious to know why you would do that?

That means if a positive or negative number is entered it displays as "yes"
and if a 0 is entered it displays as "no".

And if the TEXT entries "yes" or "no" are entered they aren't affected.

Are there TEXT entries "yes" and/or "no" in this range?

Perhaps you need to explain a little more.


--
Biff
Microsoft Excel MVP


"ksibly" wrote in message
...
I have a colum D3 thru D292 which has answer yes or no. I formated the
cells
to custom "yes";"yes";"no".

I want to have a formula that counts the number of yes's and no's. Then
formula to show the percentage yes vers no answers of the total of 292
answers.

formula with yes and no
 

You should have left it as GENERAL.

Formatted as GENERAL and if you're entering the text values of "yes" or "no"
then Joel's formulas will/should work.

--
Biff
Microsoft Excel MVP


"Ksibly" wrote in message
...
New at this thought to get the correct number would need to change the
cell
format. Should I have left it a general or Text?

"T. Valko" wrote:

I formated the cells to custom "yes";"yes";"no".

I'm curious to know why you would do that?

That means if a positive or negative number is entered it displays as
"yes"
and if a 0 is entered it displays as "no".

And if the TEXT entries "yes" or "no" are entered they aren't affected.

Are there TEXT entries "yes" and/or "no" in this range?

Perhaps you need to explain a little more.


--
Biff
Microsoft Excel MVP


"ksibly" wrote in message
...
I have a colum D3 thru D292 which has answer yes or no. I formated the
cells
to custom "yes";"yes";"no".

I want to have a formula that counts the number of yes's and no's.
Then
formula to show the percentage yes vers no answers of the total of 292
answers.