COUNTIF(range,=range_name)
 

I'm working on interest rate scenarios. I have a range defined by cells
($n$20:$n$2000) with a formulas that chooses the lower of the current
interest rate or a named range (a singe cell) of "IR". Now I want to find
out how many are still below IR. I've tried
countif($n$20:$n$2000,"<IR")
but it returns 0.

It DOES work if instead of IR I enter the actual new rate (say, "4%"), but
since IR can change with the scenario, that doesn't seem efficient.

I appreciate your time and help!

-- cp

COUNTIF(range,=range_name)
 

Hi,

the syntax is like this

=COUNTIF($N$20:$N$2000,"<"&IR)

Mike

"cp" wrote:

I'm working on interest rate scenarios. I have a range defined by cells
($n$20:$n$2000) with a formulas that chooses the lower of the current
interest rate or a named range (a singe cell) of "IR". Now I want to find
out how many are still below IR. I've tried
countif($n$20:$n$2000,"<IR")
but it returns 0.

It DOES work if instead of IR I enter the actual new rate (say, "4%"), but
since IR can change with the scenario, that doesn't seem efficient.

I appreciate your time and help!

-- cp

COUNTIF(range,=range_name)
 

Try it like this:

=COUNTIF($N$20:$N$2000,"<"&IR)

--
Biff
Microsoft Excel MVP


"cp" wrote in message
...
I'm working on interest rate scenarios. I have a range defined by cells
($n$20:$n$2000) with a formulas that chooses the lower of the current
interest rate or a named range (a singe cell) of "IR". Now I want to find
out how many are still below IR. I've tried
countif($n$20:$n$2000,"<IR")
but it returns 0.

It DOES work if instead of IR I enter the actual new rate (say, "4%"), but
since IR can change with the scenario, that doesn't seem efficient.

I appreciate your time and help!

-- cp

COUNTIF(range,=range_name)
 

That works! Thanks so much!

"cp" wrote:

I'm working on interest rate scenarios. I have a range defined by cells
($n$20:$n$2000) with a formulas that chooses the lower of the current
interest rate or a named range (a singe cell) of "IR". Now I want to find
out how many are still below IR. I've tried
countif($n$20:$n$2000,"<IR")
but it returns 0.

It DOES work if instead of IR I enter the actual new rate (say, "4%"), but
since IR can change with the scenario, that doesn't seem efficient.

I appreciate your time and help!

-- cp

COUNTIF(range,=range_name)
 

You're welcome!

--
Biff
Microsoft Excel MVP


"cp" wrote in message
...
That works! Thanks so much!

"cp" wrote:

I'm working on interest rate scenarios. I have a range defined by cells
($n$20:$n$2000) with a formulas that chooses the lower of the current
interest rate or a named range (a singe cell) of "IR". Now I want to
find
out how many are still below IR. I've tried
countif($n$20:$n$2000,"<IR")
but it returns 0.

It DOES work if instead of IR I enter the actual new rate (say, "4%"),
but
since IR can change with the scenario, that doesn't seem efficient.

I appreciate your time and help!

-- cp