CountIf from drop down list

[mlevar]

I have a drop down list with 3 option, (Yes, No or DK). I am trying to count the number of times Yes appears in the column. I'm certain that I have the countif formula set up correctly. However, I am getting a value of O as a result. I get the same result if I change the Criteria to No or DK also. Is the drop down list the issue? Here is the formula:

'=COUNTIF(N2:N198,"Yes")

Thanks,

Marcelle

 

[Jonmo1]

The formula looks right.

Can you copy/paste the contents of the "Source" box from Validation into the thread?

 

[Ron Coderre]

It seems like list value may have an extra space?

Try this...
Select the "Yes" item...Click in the Formula Bar
Is there an extra space before or after "Yes"?

"Yes " versus "Yes"

Does that help?

 

[mlevar]

Sure, the name of the drop down list is =cellcomp and the contents of the source list is:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17 x:str="Yes ">Yes </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="No ">No </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>DK</TD></TR></TBODY></TABLE>

 

[mlevar]

It seems like list value may have an extra space?

Try this...
Select the "Yes" item...Click in the Formula Bar
Is there an extra space before or after "Yes"?

"Yes " versus "Yes"

Does that help?

Yes, that worked. Thank You!!

 

[Jonmo1]

OK, so cellcomp is a Named Range from Inert - Name - Define ?

Check the source data for extra spaces as Ron Pointed out.

 

[mlevar]

Thanks! Adding the space to the formula worked.

 

[Jonmo1]

Thanks! Adding the space to the formula worked.

Glad it works, but I must say you would be better off correcting the Data at the source, not in the formula. If you ever have to use that data for another formula or something, you may have problems because of the extra space. Just fix it at the source and be done with it.