counting with multiple conditions

[Guest]

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?

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP

 

[Guest]

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

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

 

[Guest]

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\)]

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

 

[Gord Dibben]

Good point Rick

Forgot I was in programming group.

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


Gord

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Chip Pearson]

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)