Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Countif orSumif or what?
I'd like to look at a range of columns on each row and count the numbe of
occurances of "R", "D", etc. I've tried Countif and Sumif and get zero back. I've tried looking for singles ("D") and still get zero. Any ideas? Thanks, Doug |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Countif orSumif or what?
What did your formulas look like?
Something like this: =countif(2:2,"D") Try this instead: =countif(2:2,"*D*") If you get a value other than 0, then I bet you have other stuff in those cells--not just a single character. In "*D*", the asterisks are wildcards and represent any number of other characters. So maybe you have some extra space characters (or even HTML non-breaking spaces) in each of those cell. Saved from a previous post. Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.aspx Depending on what that character is, you may be able to use alt-#### (from the number keypad) to enter the character into the Other box in the text to columns wizard dialog. In fact, you may be able to select the character (in the formula bar), and copy it. Then use ctrl-v to paste into that text to columns Other box. You may be able to use Edit|Replace to change the character--Some characters can be entered by holding the alt-key and typing the hex number on the numeric keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've never been able to get alt-0013 to work for carriage returns. Another alternative is to fix it via a formula: =substitute(a1,char(##),"") Replace ## with the ASCII value you see in Chip's addin. Or you could use a macro (after using Chip's CellView addin): Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim myGoodChars As Variant Dim iCtr As Long myBadChars = Array(Chr(##)) '<--What showed up in CellView? myGoodChars = Array("") If UBound(myGoodChars) < UBound(myBadChars) Then MsgBox "Design error!" Exit Sub End If For iCtr = LBound(myBadChars) To UBound(myBadChars) ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _ Replacement:=myGoodChars(iCtr), _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False Next iCtr End Sub If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Doug Mc wrote: I'd like to look at a range of columns on each row and count the numbe of occurances of "R", "D", etc. I've tried Countif and Sumif and get zero back. I've tried looking for singles ("D") and still get zero. Any ideas? Thanks, Doug -- Dave Peterson |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Countif orSumif or what?
Hi Dave,
You've helped me before. Thanks again. I really want to count it if has just a D in it and no other characters. Doug "Dave Peterson" wrote in message ... What did your formulas look like? Something like this: =countif(2:2,"D") Try this instead: =countif(2:2,"*D*") If you get a value other than 0, then I bet you have other stuff in those cells--not just a single character. In "*D*", the asterisks are wildcards and represent any number of other characters. So maybe you have some extra space characters (or even HTML non-breaking spaces) in each of those cell. Saved from a previous post. Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.aspx Depending on what that character is, you may be able to use alt-#### (from the number keypad) to enter the character into the Other box in the text to columns wizard dialog. In fact, you may be able to select the character (in the formula bar), and copy it. Then use ctrl-v to paste into that text to columns Other box. You may be able to use Edit|Replace to change the character--Some characters can be entered by holding the alt-key and typing the hex number on the numeric keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've never been able to get alt-0013 to work for carriage returns. Another alternative is to fix it via a formula: =substitute(a1,char(##),"") Replace ## with the ASCII value you see in Chip's addin. Or you could use a macro (after using Chip's CellView addin): Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim myGoodChars As Variant Dim iCtr As Long myBadChars = Array(Chr(##)) '<--What showed up in CellView? myGoodChars = Array("") If UBound(myGoodChars) < UBound(myBadChars) Then MsgBox "Design error!" Exit Sub End If For iCtr = LBound(myBadChars) To UBound(myBadChars) ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _ Replacement:=myGoodChars(iCtr), _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False Next iCtr End Sub If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Doug Mc wrote: I'd like to look at a range of columns on each row and count the numbe of occurances of "R", "D", etc. I've tried Countif and Sumif and get zero back. I've tried looking for singles ("D") and still get zero. Any ideas? Thanks, Doug -- Dave Peterson |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Countif orSumif or what?
I forgot to include the formula I'm using:
=COUNTIF($AT3810:$FI3810,"D ") "Dave Peterson" wrote in message ... What did your formulas look like? Something like this: =countif(2:2,"D") Try this instead: =countif(2:2,"*D*") If you get a value other than 0, then I bet you have other stuff in those cells--not just a single character. In "*D*", the asterisks are wildcards and represent any number of other characters. So maybe you have some extra space characters (or even HTML non-breaking spaces) in each of those cell. Saved from a previous post. Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.aspx Depending on what that character is, you may be able to use alt-#### (from the number keypad) to enter the character into the Other box in the text to columns wizard dialog. In fact, you may be able to select the character (in the formula bar), and copy it. Then use ctrl-v to paste into that text to columns Other box. You may be able to use Edit|Replace to change the character--Some characters can be entered by holding the alt-key and typing the hex number on the numeric keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've never been able to get alt-0013 to work for carriage returns. Another alternative is to fix it via a formula: =substitute(a1,char(##),"") Replace ## with the ASCII value you see in Chip's addin. Or you could use a macro (after using Chip's CellView addin): Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim myGoodChars As Variant Dim iCtr As Long myBadChars = Array(Chr(##)) '<--What showed up in CellView? myGoodChars = Array("") If UBound(myGoodChars) < UBound(myBadChars) Then MsgBox "Design error!" Exit Sub End If For iCtr = LBound(myBadChars) To UBound(myBadChars) ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _ Replacement:=myGoodChars(iCtr), _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False Next iCtr End Sub If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Doug Mc wrote: I'd like to look at a range of columns on each row and count the numbe of occurances of "R", "D", etc. I've tried Countif and Sumif and get zero back. I've tried looking for singles ("D") and still get zero. Any ideas? Thanks, Doug -- Dave Peterson |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Countif orSumif or what?
Provide some examples about your data structure. Confirm whether each cell
consist only the Letters "D" OR "R" ? Otherwise whether the D or R is mingled some other words or Character? For Example if the cell value is like this KLDMDSRORR Whether you need to check whether 'D' OR 'R' is present on that cell or if you want to count the Number of Occurences of 'D' OR 'R' in each cells. -------------------- (Ms-Exl-Learner) -------------------- "Doug Mc" wrote: I'd like to look at a range of columns on each row and count the numbe of occurances of "R", "D", etc. I've tried Countif and Sumif and get zero back. I've tried looking for singles ("D") and still get zero. Any ideas? Thanks, Doug . |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Countif orSumif or what?
I've tried looking for singles ("D") and still get zero.
Could be invisible leading/trailing white spaces somewhere throwing things off Try TRIM, eg: =SUMPRODUCT(--(TRIM(A2:A10)="D")) Any good? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Doug Mc" wrote: I'd like to look at a range of columns on each row and count the numbe of occurances of "R", "D", etc. I've tried Countif and Sumif and get zero back. I've tried looking for singles ("D") and still get zero. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF - #DIV/01 | Excel Discussion (Misc queries) | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |