Counting Characters in cells

[Brian Bermingham]

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

 

[Ron Rosenfeld]

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

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

 

[RagDyeR]

Try this:

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

HTH,

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


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

 

[RagDyeR]

Adjust for your range:

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

--

HTH,

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

Try this:

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

HTH,

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


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

 

[Brian Bermingham]

On Tue, 23 Jun 2009 08:11:01 -0700, Brian Bermingham <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

 

[Brian Bermingham]

Thanks RD that works as well.
Brian

 

[RagDyeR]

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

HTH,

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

message Thanks RD that works as well.
Brian

 

[RagDyeR]

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

message

On Tue, 23 Jun 2009 08:11:01 -0700, Brian Bermingham <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

 

[Ron Rosenfeld]

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

 

[Brian Bermingham]

Thanks for taking the time to explain.

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