countif

  • Thread starter Thread starter Hawk
  • Start date Start date
H

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

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...
 
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))
 
Hi Hawk,
1) =COUNTIF(range, ">5") - COUNTIF(range,">9"
or =SUMPRODUCT(--(range>5),--(range<9))

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

Best wishes
Bernard
 
Back
Top