Use 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

Use of the COUNTIF function
 

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" wrote in message
...
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

Use of the COUNTIF function
 

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

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

Use of the COUNTIF function
 

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" wrote in message
...
YES! That Worked.
Can you expound on the meaning of Ctrl-****-Enter vs. Enter.

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

Use of the COUNTIF function
 

Many Thanks!
That was very informative!
JCameron

Use of the COUNTIF function
 

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


"JCameron" wrote in message
...
Many Thanks!
That was very informative!
JCameron

Use of the COUNTIF function
 

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!

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
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" wrote in message
...
YES! That Worked.
Can you expound on the meaning of Ctrl-****-Enter vs. Enter.

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

Use of the COUNTIF function
 

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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!

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
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" wrote in message
...
YES! That Worked.
Can you expound on the meaning of Ctrl-****-Enter vs. Enter.

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

Use of the COUNTIF function
 

Well, not exactly. SUM ignores logical (TRUE or FALSE) when they're an

array or a cell reference
=SUM({TRUE;TRUE}) = 0


Perhaps a workaround:

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

--
Dana DeLouis


"T. Valko" wrote in message ...

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!

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...

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" wrote in message
...

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

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

Use of the COUNTIF function
 

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


"T. Valko" wrote in message
...
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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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!

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote
in message ...
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" wrote in message
...
YES! That Worked.
Can you expound on the meaning of Ctrl-****-Enter vs. Enter.

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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

Use of the COUNTIF function
 

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.

Use of the COUNTIF function
 

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

--
Graewood Business Services, Kwinana, Perth, Western Australia


"Rick Rothstein (MVP - VB)" wrote:

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


"JCameron" wrote in message
...
Many Thanks!
That was very informative!
JCameron