Trim Spaces or Use Checkboxes

[Thomas5000]

have a column of cells where users put in "Y" for yes and "N" for no. However, when i count the Y's and N's, if there is a space after the letter, it will not count the character. I can't depend on users to remove the spaces,they are not that computer literate. Besides spaces are hard to see.

I'm actually using the sumproduct function instead of count but i figured the method would be similar but just in case, i thought i would make sure.

How can i trim the spaces? So that "Y" will be treated the same as " Y "?

I found this function called trim() but it will not allow me to do
=COUNTIF(trim(A2:A5),"y") and i can't format the cell to trim automatically that i know of.

Thanks!

 

[Gavona]

A few ideas:

Use Data,Validation,list so that users can only input values you specify (and can choose from drop down list.

Use data validation after you have received the file, or with only a warning if invalid data is entered.
Then open the formula auditing toolbar (Tools,Formula Auditing, Show Formula Auditing Toolbar)
Use the "Circle invalid data button"

Use Edit replace to replace all spaces in the column with blanks

Use Autofilter with the criteria: contains Y
Highlight the filtered cells, type Y then Ctrl-Enter. This will replace the contents of all the highlighted cells

Insert a new column B and fill with the formula =Trim(..)
Select the column.
Edit copy
Edit, pasteSpecial, values

Gavin

 

[Gavona]

Come to think of it Sumproduct does work. For example
=SUMPRODUCT(--(TRIM(A4:A6)="y"),B4:B6)

Post more detail if you can't get it to work. Including your formula that does not work.

Gavin