compound statement syntax

Posted by Tom_F on December 16, 2001 5:06 AM

I wish to count all the numbers greater than 10 and less than 15 from cells 1 to 20 in column a.

I tried this statement but to no avail.

countif(and(a1:a20>=10,a1:a20&LT;15))

What is the proper syntax for this statement?

I appreciate your expertise, experience and help,
Tom_F

Posted by Aladin Akyurek on December 16, 2001 5:53 AM

Tom --

There was a similar question posted at this site very recently. By doing a search with CONTROL+F on COUNTIF, for example, you could have discovered the answer, whic is:

=COUNTIF(A1:A20,">10")-COUNTIF(A1:A20,">=15")

This will give an exclusive between count.

If you place 10 in say B1 and 15 in B2, you can rewrite the formula as:

=COUNTIF(A1:A20,">"&B1)-COUNTIF(A1:A20,">="&B2)

Aladin

==========

Posted by Paul on December 16, 2001 6:07 AM

Try this array formula SUM((A1:A20&LT;=14)*(A1:A20>=10)) you must hit ctrl, shift, and enter to use this

Posted by Aladin Akyurek on December 16, 2001 6:24 AM

Paul --

There is no need for using an expensive formula in this case.

Regards.

Aladin

=======

Posted by Tom_F on December 16, 2001 6:52 AM

bingo!

Thanks Aladin! (wondering where you hide your magic lamp ...)

Being a newbie to both Excel and this MB I have learned a lot.

I was sooo stuck on the syntax of trying to form the conjunction for the two inequalities that I could not break out of my box. It seems as if it would be possible to configure the conjunction of two inequalities with the countif using the 'and' worksheet function but maybe not. Your solution makes sense and is easy to understand.

Now to figure out what B1 and "&" symbol denotes in your response. (scratching head ...)

Thanks for the tip on the search function also.

Thanks again,
Tom_F

Posted by Tom_F on December 16, 2001 6:55 AM

thanks (nt)

now i have two ways.

Posted by Paul on December 16, 2001 10:16 AM

Thanks for the tip, posted mine before I seen yours