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

=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" wrote in
message ...

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

On Tue, 20 Sep 2005 05:53:54 -0500, elite
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

Thanks guys


--
elite
------------------------------------------------------------------------
elite's Profile: http://www.excelforum.com/member.php...o&userid=27394
View this thread: http://www.excelforum.com/showthread...hreadid=469123