 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Use of the COUNTIF function
I currently have a cell with the current definition: =COUNTIF(E9:E200,">1000") which yeilded the number of cells that have a value greater than 1000. Now the data in this field has been changed to an alpha numeric string and I would like to change the COUNTIF to count the number of cells where the string length is equal to 4 characters. I have tried various things like: COUNTIF(E9:E200,"LEN($)=4") whith no luck. Any suggestions or solutions would be appreciated. JCameron Give this array-entered** formula a try... =SUM(IF(LEN(E8:J15)=4,1)) ** Commit this formula by pressing Ctrl+Shift+Enter, not just Enter. Rick >I currently have a cell with the current definition: > =COUNTIF(E9:E200,">1000") [quoted text clipped - 5 lines] > whith no luck. Any suggestions or solutions would be appreciated. > JCameron YES! That Worked. Can you expound on the meaning of Ctrl-sh.t-Enter vs. Enter. > Give this array-entered** formula a try... > [quoted text clipped - 13 lines] > > whith no luck. Any suggestions or solutions would be appreciated. > > JCameron It forces functions within a formula that normally cannot process a range of values to process them one cell at a time and return an array of values. So, in the formula I posted, the LEN function, which normally takes a single text string, is forced to look at each text string in each cell of the range E8:J15 and return the length it finds in them, as an array of numbers. These returned values are compared, one at a time to the value 4 and the IF statement looks at each of these comparisons, again one at a time, and returns 1 or (since I omitted the false argument to the IF statement) FALSE (which SUM will convert to 0 since it will attempt to perform a mathematical operation on it)... this array of 1's and 0's will be added up by the SUM function and that result will be returned to you. Remember, each 1 was generated when the length of an individual cell's text was 4, all other length returned FALSE which was evaluated as 0; hence, the returned value was the number of cells containing text that was exactly 4 characters long. Rick > YES! That Worked. > Can you expound on the meaning of Ctrl-sh.t-Enter vs. Enter. [quoted text clipped - 19 lines] >> > whith no luck. Any suggestions or solutions would be appreciated. >> > JCameron Many Thanks! That was very informative! JCameron You are welcome. By the way, you might find this interesting - you can get Excel to show you the elements of the array it is producing at any step in the calculation process (useful when debugging an array-entered formula you might be trying to construct). Select the cell with my formula in it and select (highlight) exactly this text in the formula... LEN(E8:J15) Then press F9 and you will see all the individual calculated lengths within the range. When you are done looking at the array values, press Esc (ALWAYS press Esc when done, otherwise your formula will be modified to show the actual array values rather than the formula that produced the array values). Next, select (highlight) exactly this text in the formula... LEN(E8:J15)=4 and you will see the array of TRUE/FALSE (the evaluations of the individual logical expressions) from the individual comparisons of the lengths of each cell in E8:J15 against the constant value of 4. REMEMBER, press Esc when you are done looking at the array elements. One more.... select exactly this text from the formula... IF(LEN(E8:J15)=4,1) then press F9 and you will see the array elements the the SUM formula will add up. Notice these array elements are composed of 1's and FALSE's. That's it... REMEMBER to press F9 when done looking at the array elements. Also, remember, that whenever you enter the formula bar of an array-entered formula, you MUST leave the formula bar by pressing Ctrl+Shift+Enter... you must do that every time or you will lose the array evaluated calculation. Rick > Many Thanks! > That was very informative! > JCameron >returns......FALSE (which SUM will convert to 0 >since it will attempt to perform a mathematical operation on it)... Well, not exactly. SUM ignores logicals (TRUE or FALSE) when they're an array or a cell reference. Since these logicals are FALSE the net effect is the same. A1 = TRUE A2 = TRUE =SUM(A1:A2) = 0 =SUM(A1) = 0 =SUM(TRUE,TRUE) = 2 =SUM({TRUE;TRUE}) = 0 However, I can't think of a real-world scenario where you'd use something like this: =SUM(10,TRUE,10,FALSE) = 21 It might make a good question on a test!  SignatureBiff Microsoft Excel MVP > It forces functions within a formula that normally cannot process a range > of values to process them one cell at a time and return an array of [quoted text clipped - 38 lines] >>> > whith no luck. Any suggestions or solutions would be appreciated. >>> > JCameron P.S. If you read Excel (2002) help on SUM: ---------- If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored. ---------- The part about ignoring error values is incorrect. SignatureBiff Microsoft Excel MVP > >returns......FALSE (which SUM will convert to 0 >>since it will attempt to perform a mathematical operation on it)... [quoted text clipped - 61 lines] >>>> > whith no luck. Any suggestions or solutions would be appreciated. >>>> > JCameron Hah! One of the problems with being self-taught... I was fooled by the "logic" of things... I figured since nothing was changing with the FALSE's, it must have been because they were contributing 0 to the totals, not that they were just being ignored. Thanks for pointing this out Biff. Rick > P.S. > [quoted text clipped - 73 lines] >>>>> > whith no luck. Any suggestions or solutions would be appreciated. >>>>> > JCameron |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.