Use of the COUNTIF function

[JCameron]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[JCameron]

YES! That Worked.
Can you expound on the meaning of Ctrl-Shit-Enter vs. Enter.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[JCameron]

Many Thanks!
That was very informative!
JCameron

 

[Rick Rothstein \(MVP - VB\)]

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

 

[T. Valko]

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!

 

[T. Valko]

P.S.

If you read Excel (2002) help on SUM:

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

Perhaps a workaround:

=SUM(--{TRUE;TRUE}) (Array Entered)
2

Which gives me an idea. Instead of the array-entered** formula I posted...

=SUM(IF(LEN(E8:J15)=4,1))

this array-entered** one will return exactly the same answer...

=SUM(--(LEN(E8:J15)=4))

Rick


** Commit by pressing Ctrl+Shift+Enter, not Enter.

 

[Philip Mark Hunt]

Hello Rick

I found your reference to highlighting and f( in this post very informative.
I wonder whether you might have some ideas regarding the problem that Harlan
has so very usefully helped me on. It is currently running in this newsgroup
as PROBLEMS WITH USING TEXT, INDIRECT AND ADDRESS WITHIN AN ARRAY FORMULA,
posted on 23rd January 2009.

Though I have solved the addressing problem, I now want Excel to take "the
array [of 1s and 0s] it is producing" and convert it into a string, and then
place that in the result cell, rather than its SUM.

Your input would be welcome.

Best regards
Philip
Medina, Kwinana, Perth, Western Australia