At this time I don't need to use the COUNTIF
unless you have a specific reason.

Yes, the reason is very specific!

You *can't* build formulas by concatenating all the components like you're
trying to do. When you concatenate all the components all you have is a TEXT
string that looks like a formula, but it's not a formula.

COUNTIF is one of a couple of functions where you can build the criteria
expression through concatenation. And, since you seem to be comparing
numeric values, COUNTIF makes it possible to do what you want (as far as I
understand what you want to do).

To build a cell/range reference you *must* use the INDIRECT function.

When I use this formula in a cell it works:
IF("C"&Concatenate((ROW())="",if(H45=0.4,"="," "))

Are you sure that formula works? It's missing a closing ) for the
CONCATENATE function. Also, you're not really referencing "C&ROW()" as a
cell reference. It's just a TEXT string that looks like a cell reference as
I noted above.

Let's assume that formula is entered in cell A1. So:

"C"&Concatenate((ROW())) evaluates to the TEXT string "C1". Note the
quotations marks around "C1". "C1" IS NOT being evaluated as a valid cell
reference.

To get a valid cell reference you'd have to use INDIRECT:

INDIRECT("C"&ROW())

IF("C"&Concatenate(ROW()"",IF(Concatenate($AS$2 &Row()&$AS$4&$AS$3),$AS$4,""),"123")

Nah, that isn't going to work for the reason(s) I explained above.

Try it like this:

=IF(INDIRECT("C"&ROW())"",IF(COUNTIF(INDIRECT($A S$2&Row()),""&$AS$4&$AS$3&""),$AS$4,""),123)

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
Thanks for the responses...I've updated things but I'm still having
issues.
At this time I don't need to use the COUNTIF unless you have a specific
reason. I just need to know, by row, IF the value I'm looking at is ,
=,
=, , = than the value I'm comparing to.
When I use this formula in a cell it works:
IF("C"&Concatenate((ROW())="",if(H45=0.4,"=","" ))
but if I use this formula for $AS$4="=" and $AS$3="0.4" it does NOT work:
IF("C"&Concatenate(ROW()"",IF(Concatenate($AS$2& Row()&$AS$4&$AS$3),$AS$4,""),"123")
I still get the #VALUE error.
The calculation steps show:
IF(TRUE,if("H45=0.4",$AS$4,"??"),"123") and then the next step shows the
#VALUE error as IF(TRUE,#Value!,"123"). Keep in mind that the cells are
General for $AS$3 and $AS$4.
What am I doing wrong here? Seems so simple but I can't pierce the issue!
;-(
TIA

"T. Valko" wrote:

Improvement

=COUNTIF(A1,CONCATENATE("",E$1,F$1,""))

We can eliminate the CONCATENATE function:

=COUNTIF(A1,""&E$1&F$1&"")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
See if this points you in the right direction.

You want to identify numbers in a range of cells that are 50.

E1 =
F1 = 50

A1 = 50
A2 = 99
A3 = 44
A4 = 66
A5 = 50.5

Enter this formula in B1 and copy down to B5:

=COUNTIF(A1,CONCATENATE("",E$1,F$1,""))

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
Excel 2007 all MS updates
I need to search ANY column of numbers, say Column AL, to determine if
any
of the values are below or above a value that I'm wanting to find.

Here's an example: in Column AL the values are 1.20, 1.50, 1.75, 1.10,
1.95,
1.55. I'd like to find all cells that have a value of less than 1.50
(not
less than or equal in this example). Hence, for Column AM, I'd
normally
use
something like =IF(AL11.50,"True","False") for each of the cells of
AM1,
AM2, AM3, AM4, AM5, AM6 etc... I'd get a "True" for 1.20, 1.10 and a
"False"
for 1.50, 1.75, 1.95, 1.55.

I'd like to GENERALIZE this by setting up a cell for the OPERATION
(less
than, less than or equal to, equal to, greater than, greater than or
equal
to) and another cell for the VALUE I'm looking for (the 1.50 in the
above
example and a cell for the COLUMN I'm wanting to compare.

I'm struggling with the structure of the formula. Here's what I
currently
have:
=IF(H3675"",IF(AL3675=$A$3,"LE",""),""). [I change ALL 4000+
entries
EACH time I want to do a comparison!) H3675 here is a cell that
contains
a
date, AL3675 is cell #3675 in column AL that contains a value (like
the
1.20,
1.10 etc above); = is my "less than or equal here" that I'd like to
change
to be a cell value in, say Cell AL1 where I put the =; $A$3 is my
value
that
I use (the 1.50 used in the above example-I can do this!); the "LE" is
less
than or equal etc. (I'll probably need an LE, LT, EQ, GE, GT).
Any ideas on approaching would be appreciated as the Excel FIND
doesn't
do
value comparisons and I'd like to make my comparisons variable by
COLUMN
(AL
etc), OPERATION (LE, LT etc.) and VALUE (1.50 etc.).

TIA





.