Counting Characters in cells
 

Hi

I want to count the number of cells containing a particular uppercase
character.
I tried using =COUNTIF(J2:AN2,"H") but this counts lowercase as well.
Is there any way to count upper case or lower case only?

I am using Excel 2007

Thanks
Brian

Counting Characters in cells
 

On Tue, 23 Jun 2009 08:11:01 -0700, Brian Bermingham <Brian
wrote:

Hi

I want to count the number of cells containing a particular uppercase
character.
I tried using =COUNTIF(J2:AN2,"H") but this counts lowercase as well.
Is there any way to count upper case or lower case only?

I am using Excel 2007

Thanks
Brian


Try:

=SUMPRODUCT(--ISNUMBER(FIND("H",J2:AN2)))

--ron

Counting Characters in cells
 

Try this:

=SUMPRODUCT(--EXACT("H",J2:Z2))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Brian Bermingham" <Brian wrote in
message ...
Hi

I want to count the number of cells containing a particular uppercase
character.
I tried using =COUNTIF(J2:AN2,"H") but this counts lowercase as well.
Is there any way to count upper case or lower case only?

I am using Excel 2007

Thanks
Brian

Counting Characters in cells
 

Adjust for your range:

=SUMPRODUCT(--EXACT("H",J2:AN2))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"RagDyeR" wrote in message
...
Try this:

=SUMPRODUCT(--EXACT("H",J2:Z2))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Brian Bermingham" <Brian wrote in
message ...
Hi

I want to count the number of cells containing a particular uppercase
character.
I tried using =COUNTIF(J2:AN2,"H") but this counts lowercase as well.
Is there any way to count upper case or lower case only?

I am using Excel 2007

Thanks
Brian

Counting Characters in cells
 

"Ron Rosenfeld" wrote:

On Tue, 23 Jun 2009 08:11:01 -0700, Brian Bermingham <Brian
wrote:

Hi

I want to count the number of cells containing a particular uppercase
character.
I tried using =COUNTIF(J2:AN2,"H") but this counts lowercase as well.
Is there any way to count upper case or lower case only?

I am using Excel 2007

Thanks
Brian


Try:

=SUMPRODUCT(--ISNUMBER(FIND("H",J2:AN2)))

--ron


Thanks ron that woks fine.
Just so I understand, the character is being compared as a number and as
upper case and lower case have diffeten values only the specified version is
counted?

Thanks

Brian

Counting Characters in cells
 

Thanks RD that works as well.
Brian

"RagDyeR" wrote:

Adjust for your range:

=SUMPRODUCT(--EXACT("H",J2:AN2))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"RagDyeR" wrote in message
...
Try this:

=SUMPRODUCT(--EXACT("H",J2:Z2))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Brian Bermingham" <Brian wrote in
message ...
Hi

I want to count the number of cells containing a particular uppercase
character.
I tried using =COUNTIF(J2:AN2,"H") but this counts lowercase as well.
Is there any way to count upper case or lower case only?

I am using Excel 2007

Thanks
Brian

Counting Characters in cells
 

You're welcome, and appreciate the feed-back.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Brian Bermingham" wrote in
message ...
Thanks RD that works as well.
Brian

"RagDyeR" wrote:

Adjust for your range:

=SUMPRODUCT(--EXACT("H",J2:AN2))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"RagDyeR" wrote in message
...
Try this:

=SUMPRODUCT(--EXACT("H",J2:Z2))
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Brian Bermingham" <Brian wrote in
message ...
Hi

I want to count the number of cells containing a particular uppercase
character.
I tried using =COUNTIF(J2:AN2,"H") but this counts lowercase as well.
Is there any way to count upper case or lower case only?

I am using Excel 2007

Thanks
Brian

Counting Characters in cells
 

If I may:

The Find() function is itself, case sensitive.
It will only locate the position of the exact character in the first
argument.

The position, being a number, is returned as TRUE by the Isnumber()
function, and Sumproduct counts the number of TRUEs.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Brian Bermingham" wrote in
message ...


"Ron Rosenfeld" wrote:

On Tue, 23 Jun 2009 08:11:01 -0700, Brian Bermingham <Brian
wrote:

Hi

I want to count the number of cells containing a particular uppercase
character.
I tried using =COUNTIF(J2:AN2,"H") but this counts lowercase as well.
Is there any way to count upper case or lower case only?

I am using Excel 2007

Thanks
Brian


Try:

=SUMPRODUCT(--ISNUMBER(FIND("H",J2:AN2)))

--ron


Thanks ron that woks fine.
Just so I understand, the character is being compared as a number and as
upper case and lower case have diffeten values only the specified version is
counted?

Thanks

Brian

Counting Characters in cells
 

On Tue, 23 Jun 2009 08:42:02 -0700, Brian Bermingham
wrote:



"Ron Rosenfeld" wrote:

On Tue, 23 Jun 2009 08:11:01 -0700, Brian Bermingham <Brian
wrote:

Hi

I want to count the number of cells containing a particular uppercase
character.
I tried using =COUNTIF(J2:AN2,"H") but this counts lowercase as well.
Is there any way to count upper case or lower case only?

I am using Excel 2007

Thanks
Brian


Try:

=SUMPRODUCT(--ISNUMBER(FIND("H",J2:AN2)))

--ron


Thanks ron that woks fine.
Just so I understand, the character is being compared as a number and as
upper case and lower case have diffeten values only the specified version is
counted?

Thanks

Brian

Yes, only the Upper case is being counted.

FIND is a case-sensitive function. (If you want a case-insensitive variant,
use SEARCH instead).

If FIND locates a match, it returns a number, if it does NOT find a match, it
returns an #VALUE! error.

ISNUMBER converts the VALUE errors to FALSE; and the numeric returns to TRUE.

The double unary (--...) converts the TRUEs and FALSEs to 1's and 0's.

The SUMPRODUCT adds up all the 1's to give you the number of cells in which "H"
was matched.
--ron

Counting Characters in cells
 

Thanks for taking the time to explain.

Brian

"Ron Rosenfeld" wrote:

On Tue, 23 Jun 2009 08:42:02 -0700, Brian Bermingham
wrote:



"Ron Rosenfeld" wrote:

On Tue, 23 Jun 2009 08:11:01 -0700, Brian Bermingham <Brian
wrote:

Hi

I want to count the number of cells containing a particular uppercase
character.
I tried using =COUNTIF(J2:AN2,"H") but this counts lowercase as well.
Is there any way to count upper case or lower case only?

I am using Excel 2007

Thanks
Brian


Try:

=SUMPRODUCT(--ISNUMBER(FIND("H",J2:AN2)))

--ron


Thanks ron that woks fine.
Just so I understand, the character is being compared as a number and as
upper case and lower case have diffeten values only the specified version is
counted?

Thanks

Brian

Yes, only the Upper case is being counted.

FIND is a case-sensitive function. (If you want a case-insensitive variant,
use SEARCH instead).

If FIND locates a match, it returns a number, if it does NOT find a match, it
returns an #VALUE! error.

ISNUMBER converts the VALUE errors to FALSE; and the numeric returns to TRUE.

The double unary (--...) converts the TRUEs and FALSEs to 1's and 0's.

The SUMPRODUCT adds up all the 1's to give you the number of cells in which "H"
was matched.
--ron