More Help Needed with Count formula

[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

[Gary's Student]

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
--
Gary's Student


"Greegan" wrote:

> 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
>
>
>

[David McRitchie]

if you have the checkmarks in say A2:A34
you can place the following formula in A35

A35: =COUNTIF(a$2:OFFSET(A35,-1,0),"a")

choice of checkmarks found in font tables
http://www.mvps.org/dmcritchie/rexx/...bols.htm#ticks
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Greegan" <[email protected]> wrote in message news:[email protected]...
> 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
>
>

[David McRitchie]

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).

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"David McRitchie" <[email protected]> wrote in message news:[email protected]...
> if you have the checkmarks in say A2:A34
> you can place the following formula in A35
>
> A35: =COUNTIF(a$2:OFFSET(A35,-1,0),"a")
>
> choice of checkmarks found in font tables
> http://www.mvps.org/dmcritchie/rexx/...bols.htm#ticks
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Greegan" <[email protected]> wrote in message news:[email protected]...
> > 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
> >
> >
>
>

[Greegan]

Would you believe it shows no length with =LEN(B166) but it will show "2" if
the formula is =LEN(B166)+1



> 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
> --
> Gary's Student
>
>
> "Greegan" wrote:
>
>> 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
>>
>>
>>