Formula Countif

[Shona]

Does any one know a formula that will countif a number is say more than 2
but less than 5.

Cheers
Shona

 

[Peo Sjoblom]

One way

=COUNTIF(Range,">2")-COUNTIF(Range,">=5")

 

[Bernie Deitrick]

Shona,

Some variation on this will work, the variations being what you want
to do when values are exactly 2 and exactly 5:

=COUNTIF(A1:A10,">2")-COUNTIF(A1:A10,">=5")

HTH,
Bernie
MS Excel MVP

 

[Norman Harker]

Hi Shona!

One way:
=COUNTIF(A1:A10, ">2") - COUNTIF(A1:A10, ">=5")



Or:

You can use implicit if statement structures in SUMPRODUCT:

=SUMPRODUCT((A1:A10>2)*(A1:A10<5))



In this case note that A1>2 and A1<5 will evaluate as 1 or zero and
will only add to the product if both are 1.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Don Guillett]

OR
=sumproduct((a2:a22>2)*(a2:a200<5))
will get 3 & 4 . Maybe you want >=2 or <=5

 

[Shona]

Thank you

 

[Dana DeLouis]

Just to be different:

=SUM(--(ABS(rng-3.5)<1.5))

Array entered.

 

[Harlan Grove]

Just to be different:

=SUM(--(ABS(rng-3.5)<1.5))

Array entered.

...

If you're gonna be an oddball, at least use SUMPRODUCT.