Count number of cells that contain a certain letter - Case Sensitive

I have an worksheet that counts the number of cells containing a certain letter:

E.g.

Please Login or Register  to view this content.

I would like to make this case sensitive so I can return the number of lowercase 'a' and the number of uppercase 'A'

Thanks for any help

=COUNTIF($B$4:$AO$25,CHAR(97)) for "a"
=COUNTIF($B$4:$AO$25,CHAR(65)) for "A"

Regards,
Stefi

„elite” ezt �*rta:

>
> I have an worksheet that counts the number of cells containing a certain
> letter:
>
> E.g.
> Code:
> --------------------
> =(COUNTIF($B$4:$AO$25,"a"))
> --------------------
>
>
> I would like to make this case sensitive so I can return the number of
> lowercase 'a' and the number of uppercase 'A'
>
> Thanks for any help
>
>
> --
> elite
> ------------------------------------------------------------------------
> elite's Profile: http://www.excelforum.com/member.php...o&userid=27394
> View this thread: http://www.excelforum.com/showthread...hreadid=469123
>
>

try:
=SUMPRODUCT(EXACT(B2:B15,D2)+0)

where D2 contains your a or A

HTH




"elite" <elite.1vnmie_1127214305.8124@excelforum-nospam.com> wrote in
message news:elite.1vnmie_1127214305.8124@excelforum-nospam.com...
>
> I have an worksheet that counts the number of cells containing a certain
> letter:
>
> E.g.
> Code:
> --------------------
> =(COUNTIF($B$4:$AO$25,"a"))
> --------------------
>
>
> I would like to make this case sensitive so I can return the number of
> lowercase 'a' and the number of uppercase 'A'
>
> Thanks for any help
>
>
> --
> elite
> ------------------------------------------------------------------------
> elite's Profile:
> http://www.excelforum.com/member.php...o&userid=27394
> View this thread: http://www.excelforum.com/showthread...hreadid=469123
>

Thanks guys

On Tue, 20 Sep 2005 05:53:54 -0500, elite
<elite.1vnmie_1127214305.8124@excelforum-nospam.com> wrote:

>
>I have an worksheet that counts the number of cells containing a certain
>letter:
>
>E.g.
>Code:
>--------------------
> =(COUNTIF($B$4:$AO$25,"a"))
>--------------------
>
>
>I would like to make this case sensitive so I can return the number of
>lowercase 'a' and the number of uppercase 'A'
>
>Thanks for any help


The FIND worksheet function is case sensitive, and will return a number if the
string in question is found (otherwise it returns a VALUE error).

So:

=SUMPRODUCT(--ISNUMBER(FIND("A",rng)))
=SUMPRODUCT(--ISNUMBER(FIND("a",rng)))

should give you your count. The only caveat is that you cannot refer to an
entire column in your cell reference.


--ron