| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting characters | Excel Worksheet Functions | |||
| Counting Characters | Excel Worksheet Functions | |||
| Counting characters | Excel Discussion (Misc queries) | |||
| counting characters in a line | Excel Discussion (Misc queries) | |||
| counting characters issue | Excel Worksheet Functions | |||
Linear Mode
