# Using LEN function for letter by itself, not in a word or initials

Copper Contributor

# Using LEN function for letter by itself, not in a word or initials

I am working on a work schedule in excel for hospital NP shifts.  A is for day shift, P is for night shift.  I want to sum all of the cells with the letter "A" in 1 cell and "P" in another cell, but it also is counting if there is an A or P in someone's initials making it look like we have enough people on a shift when there is not.

9 Replies

# Re: Using LEN function for letter by itself, not in a word or initials

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

# Re: Using LEN function for letter by itself, not in a word or initials

@HansVogelaar here is a copy of the file

# Re: Using LEN function for letter by itself, not in a word or initials

I don't see an attachment, nor a link.

# Re: Using LEN function for letter by itself, not in a word or initials

@Krowley04 Assuming the target "A" and "P" values are always the first letter in each cell, and as long as the other codes you're using never start with "A" or "P", then the COUNTIF function would be sufficient. For example:

``````=COUNTIF(B3:B24,"A*")
=COUNTIF(B3:B24,"P*")``````

# Re: Using LEN function for letter by itself, not in a word or initials

Claiming that a range equals "A" results in true/false.

Turning that into minus and back to plus makes true/false to 1 or 0

``=SUM(--(B\$3:B\$23="A"))``

# Re: Using LEN function for letter by itself, not in a word or initials

This seems good if one takes away the wildcard/star to exclude persons initial like AJF and only count A's.

# Re: Using LEN function for letter by itself, not in a word or initials

@bosinander That's not how I interpreted the data. I made the assumption that only the superscript text was someone's initials. For example, "PNB" (where NB is formatted as superscript) would still be counted as "P" for night shift. Also, "AKP" (where KP is formatted as superscript) would be counted as "A" for morning shift, but NOT also "P" for night shift. I suppose only @Krowley04 can verify whether or not that's correct.

# Re: Using LEN function for letter by itself, not in a word or initials

You're probably right and then your solution works just fine :) My security preferences do not look positive on reusing formatted downloads.