Using 'Countif' on y/n with spaces

[gertatwork]

I'm trying to put together a workbook for auditing where there a series of questions in a column, and the user enters a y/n in the cell along the row to say where the conditions in the question have been met.

The questiosn are broken up into sections - so it's not a continuous row of y/n's - there's a total cell every few cells (not an even number) down with a countif formula that spits out a percentage according to how many yeses there are.

At the bottom theres an overall total that does the same thing.

The y/n column represents one instance of a certain task that's been audited, and there's a sheet per day with a whole heap of samples (has to be a certain percentage fo the total tasks done per day)- so there can be any number of y/n columns after the questions.

The problem I'm having is that if the user enters a space after the y/n, it's not counted by countif.

I could use data validation to restrict cell entries to JUST n/y, but I'm reluctant to do it that way as it will be a pain to add the validation to all the cells it would need to apply to, it won't be quite so user friendly and it looks pretty ugly - are there any other ways I can do what I need to do?

[Trebor76]

Hi gertatwork,

Say the "Y/N" range is B2:B10 (change as required), you could either try this formula:

=COUNTIF(B2:B10,"Y*")

which will count any text string starting with a Y, or you use the following for "Y" or "Y " responses:

=COUNTIF(B2:B10,"Y ")

HTH

Robert

[gertatwork]

HI Robert,

Thanks! It had occurred to me to use a wildcard, but for some reason I'd forgotten asterisk and was using something else (don't ask why...).

It's not working at the moment because the section totals revert to "not applicable" if there's nothing there, so the not applicable cells count in the tally, but I'm sure I can think of something else to call them that doesn't start with 'n'.