COUNTIF(range,>=range_name)

's Computer Specifications

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

's Computer Specifications

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

's Computer Specifications

Try it like this:

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

--
Biff
Microsoft Excel MVP

"cp" <(E-Mail Removed)> wrote in message
news:401BE795-858A-410D-8C15-(E-Mail Removed)...
> 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

's Computer Specifications

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

's Computer Specifications

You're welcome!

--
Biff
Microsoft Excel MVP

"cp" <(E-Mail Removed)> wrote in message
news:8115DE77-75E8-4149-82AB-(E-Mail Removed)...
> 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

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Countif using tab range	DEI	Microsoft Excel Worksheet Functions	3	12th Jan 2009 08:30 PM
COUNTIF to compare one range versus another range	Phil	Microsoft Excel Programming	3	30th May 2007 10:39 PM
Countif between a range?	=?Utf-8?B?S2VlcHJvZ29hbA==?=	Microsoft Excel Misc	2	21st Jun 2006 11:52 PM
CountIF Range	Michael	Microsoft Excel Worksheet Functions	4	31st Jan 2006 01:27 PM
COUNTIF or not to COUNTIF on a range in another sheet	=?Utf-8?B?RWxsaWU=?=	Microsoft Excel Worksheet Functions	4	15th Sep 2005 10:06 PM