How to refer to current cell in range

[Mike]

This is a simple problem, but after a couple hours of searching Excel
help and Newgroups, I can't seem to find anything helpful.

In Excel 2000

I've been using the countif function to count cells that contain a
certain phrase, e.g. this counts the cells in the range that contain
the phrase
"W2K Pro":
=COUNTIF(PCs!D2143,"W2K Pro")

What I'd like to do is count cells in which the LEN() function returns
a value greater than 12, so something like this:

=COUNTIF(PCs!D2143,LEN(currentcell)>12)

I just can't seem to find a way to refer to the current cell when the
range is processed. Anyone have any ideas?

Thanks,

Mike
..

 

[Paul]

This is a simple problem, but after a couple hours of searching Excel
help and Newgroups, I can't seem to find anything helpful.

In Excel 2000

I've been using the countif function to count cells that contain a
certain phrase, e.g. this counts the cells in the range that contain
the phrase
"W2K Pro":
=COUNTIF(PCs!D2143,"W2K Pro")

What I'd like to do is count cells in which the LEN() function returns
a value greater than 12, so something like this:

=COUNTIF(PCs!D2143,LEN(currentcell)>12)

I just can't seem to find a way to refer to the current cell when the
range is processed. Anyone have any ideas?

Thanks,

Mike
.

You can't do this with COUNTIF. COUNTIF (and SUMIF) will only take a cell
range for their comparison. You need a function of a cell range [i.e.
LEN(cell range)]. You can achieve the result you want using SUMPRODUCT:
=SUMPRODUCT((LEN(PCs!D2143)>12)*1)

 

[J.E. McGimpsey]

One way:

=SUMPRODUCT(--(LEN(PCs!D2143)>12))

 

[selowan]

Thanks for the help, it worked well. There's no way I would have
guessed from reading the help file, that SUMPRODUCT could be used in
this manner