countif

[Hawk]

I am trying to use the countif worksheet function. Lets
pretend:

Cells A1 thru A6 contain the following integers:
A1 - 4
A2 - 5
A3 - 6
A4 - 7
A5 - 8
A6 - 9

The following function returns 4
=countif(A1:A6,">5")

The first question is how do I set the criteria to:

5 and <9

The second question is involves using a range in the
criteria. If cell B1 contains the integer 5, I am not
sure how to accomplish something like the following:
=countif(A1:A6,>B5)
which I would also expect to return 4 (however the
function syntax is giving an error)

Thanks in advance for any help...

 

[Frank Kabel]

Hi
for your first question: Some options:
=countif(A1:A6,">5")-countif(A1:A6,">=9")

or
=SUMPRODUCT((A1:A6>5)*(A1:A6<9))

or
=SUMPRODUCT(--(A1:A6>5),--(A1:A6<9))

For your second question use
=countif(A1:A6,">" & B5)

in the SUMPRODUCT formulas simply exchange the value with
a cell reference. e.g.
=SUMPRODUCT(--(A1:A6>B5),--(A1:A6<B6))

 

[Peo Sjoblom]

One way

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

 

[Bernard V Liengme]

Hi Hawk,
1) =COUNTIF(range, ">5") - COUNTIF(range,">9"
or =SUMPRODUCT(--(range>5),--(range<9))

2) =COUNTIF(range, ">"&B5)

Best wishes
Bernard

 

[Hawk]

Thanks Frank...Precisely what the doctor ordered...