The below formula is working in macro.
myvar= "=countif(a1:a100,10)"
but i need to count the cells containing values between less than 10 and
greater than
five? How to change the above formula to incorporate this?

> The below formula is working in macro.
> myvar= "=countif(a1:a100,10)"
> but i need to count the cells containing values between less than 10 and
> greater than
> five? How to change the above formula to incorporate this?

How about this...

myvar= "=countif(a1:a100,""<10"")-countif(a1:a100,""<=5"")"

Note that this excludes 5 and 10 from the range. If you want to include
them, add an equal sign to the '<10' and remove the equal sign from the
'<=5'.

Rick

=COUNTIF(A1:A100,">5")-COUNTIF(A1:A100,">9")


Gord Dibben MS Excel MVP

On Sat, 14 Jul 2007 12:30:00 -0700, ezil <(E-Mail Removed)> wrote:

>The below formula is working in macro.
>myvar= "=countif(a1:a100,10)"
>but i need to count the cells containing values between less than 10 and
>greater than
>five? How to change the above formula to incorporate this?

=COUNTIF(A1:A100,">5")-COUNTIF(A1:A100,">=10")

or
ActiveCell.Formula = "=COUNTIF(A1:A100,"">5"")-COUNTIF(A1:A100,"">=10"")"

--
Regards,
Tom Ogilvy


"ezil" wrote:

> The below formula is working in macro.
> myvar= "=countif(a1:a100,10)"
> but i need to count the cells containing values between less than 10 and
> greater than
> five? How to change the above formula to incorporate this?

Thank you MR.Rick and Mr.Gord Dibben
The following formula is working and the other one is giving error message
myvar= "=countif(a1:a100,""<10"")-countif(a1:a100,""<=5"")"


"Rick Rothstein (MVP - VB)" wrote:

> > The below formula is working in macro.
> > myvar= "=countif(a1:a100,10)"
> > but i need to count the cells containing values between less than 10 and
> > greater than
> > five? How to change the above formula to incorporate this?
>
> How about this...
>
> myvar= "=countif(a1:a100,""<10"")-countif(a1:a100,""<=5"")"
>
> Note that this excludes 5 and 10 from the range. If you want to include
> them, add an equal sign to the '<10' and remove the equal sign from the
> '<=5'.
>
> Rick
>
>

The formula that Gord posted is meant to be placed directly on the
spreadsheet (no VBA macro is needed for the functionality you asked about).
The reason it is giving you an error message is you are trying to use it in
your macro and the internal quote marks are not set up correctly (inside a
text constant, you need to double up quote marks to produce a single one).

Rick


"ezil" <(E-Mail Removed)> wrote in message
news:C1160E6B-0E11-4638-8D61-(E-Mail Removed)...
> Thank you MR.Rick and Mr.Gord Dibben
> The following formula is working and the other one is giving error message
> myvar= "=countif(a1:a100,""<10"")-countif(a1:a100,""<=5"")"
>
>
> "Rick Rothstein (MVP - VB)" wrote:
>
>> > The below formula is working in macro.
>> > myvar= "=countif(a1:a100,10)"
>> > but i need to count the cells containing values between less than 10
>> > and
>> > greater than
>> > five? How to change the above formula to incorporate this?
>>
>> How about this...
>>
>> myvar= "=countif(a1:a100,""<10"")-countif(a1:a100,""<=5"")"
>>
>> Note that this excludes 5 and 10 from the range. If you want to include
>> them, add an equal sign to the '<10' and remove the equal sign from the
>> '<=5'.
>>
>> Rick
>>
>>

Good point Rick

Forgot I was in programming group.

I rarely get over here. More of a Tools>Options person.


Gord

On Sat, 14 Jul 2007 16:23:46 -0400, "Rick Rothstein \(MVP - VB\)"
<(E-Mail Removed)> wrote:

>The formula that Gord posted is meant to be placed directly on the
>spreadsheet (no VBA macro is needed for the functionality you asked about).
>The reason it is giving you an error message is you are trying to use it in
>your macro and the internal quote marks are not set up correctly (inside a
>text constant, you need to double up quote marks to produce a single one).
>
>Rick
>
>
>"ezil" <(E-Mail Removed)> wrote in message
>news:C1160E6B-0E11-4638-8D61-(E-Mail Removed)...
>> Thank you MR.Rick and Mr.Gord Dibben
>> The following formula is working and the other one is giving error message
>> myvar= "=countif(a1:a100,""<10"")-countif(a1:a100,""<=5"")"
>>
>>
>> "Rick Rothstein (MVP - VB)" wrote:
>>
>>> > The below formula is working in macro.
>>> > myvar= "=countif(a1:a100,10)"
>>> > but i need to count the cells containing values between less than 10
>>> > and
>>> > greater than
>>> > five? How to change the above formula to incorporate this?
>>>
>>> How about this...
>>>
>>> myvar= "=countif(a1:a100,""<10"")-countif(a1:a100,""<=5"")"
>>>
>>> Note that this excludes 5 and 10 from the range. If you want to include
>>> them, add an equal sign to the '<10' and remove the equal sign from the
>>> '<=5'.
>>>
>>> Rick
>>>
>>>

> Forgot I was in programming group.

Yeah, I think we have all done that a few times (think we're in one group
when actually we're in the other). And, of course, not all OPs post their
questions in the "correct" groups either, which really makes for a confusing
time (whether you are going to answer their question or not; when you move
on to the next post, the previous mis-grouped question makes you think you
are in one group instead of the other).

Rick

> Yeah, I think we have all done that a few times (think we're in one group
> when actually we're in the other).

Or, worse, thinking you're in email when you're really in news.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>> Forgot I was in programming group.
>
> Yeah, I think we have all done that a few times (think we're in one group
> when actually we're in the other). And, of course, not all OPs post their
> questions in the "correct" groups either, which really makes for a
> confusing time (whether you are going to answer their question or not;
> when you move on to the next post, the previous mis-grouped question makes
> you think you are in one group instead of the other).
>
> Rick