More Help Needed with Count formula

  • Thread starter Thread starter Greegan
  • Start date Start date
G

Greegan

I have a field in my worksheet where I want a check mark to appear. I've
decided not to use the control boxes or whichever. I have used the letter
"a" in the Font of Marlett. This appears as the check mark on the screen.

I have copied the same sheet several times and I was given the formula
=SUM(ClientA:ClientB!A1) in a previous help request which seems okay.
I've been able to use that formula and used it as a COUNT for some other
information I needed.

The problem I'm having is that I cannot seem to count the number of checks
in B166.

I've tried in a cell where it just appears white on my screen so it doesn't
print out... =IF(B166="","",IF((B166="a"),"1",B166))
I've tried counting this cell and I've tried using SUM and nothing seems to
work.

Can someone please tell me what I'm doing wrong and how I can fix it?

Thank you in advance.

G
 
You need to count the number of checks in B166.

If B166 contains nothing it would be ""
if B166 displays a single check it would contain "a"
if B166 displays two checks it would contain "aa"
if B166 displays three checks it would contain "aaa"

Try using =LEN(B166) to give the count of checks in B166
 
Okay I read it wrong but assuming that you could have other
characters in that cell such as spaces you might try something
for a count of the letter a in a cell.

keeping in mind that SUBSTITUTE itself is case sensitive
=len(a1)-len(substitute(a1,"a","")) — lettercase must match
=len(a1)-len(substitute(upper(a1),"A","")) — insensitive to lettercase,
but the case of the from in the argument must match that of the UPPER(target).
 
Would you believe it shows no length with =LEN(B166) but it will show "2" if
the formula is =LEN(B166)+1
 
Back
Top