counting with multiple conditions

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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?
Click to expand...

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,">=10")

or
ActiveCell.Formula = "=COUNTIF(A1:A100,"">5"")-COUNTIF(A1:A100,"">=10"")"
 
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"")"
 
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
 
Good point Rick

Forgot I was in programming group.

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


Gord
 
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).
Click to expand...

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)
 
Back
Top