How to define range of values in a formula?
 

A B C D E
674 817 2 0 1
-6.0
-6.0
0.1


I need to count only the cells (in the first column) wich contain values
within a specified range and display the result (if <0.5 in column C, if
between -0.5 and 0.5 in column D and if 0.5 in column E). I used the COUNTIF
function. It works well with the simple range definitions "<-0.5" and "0.5".
How should I define the range between -0.5 and 0.5? I tried this below, but
it seems I am wrong somewhere.
=COUNTIF(A30:A32;"AND(-0.5,<0.5)").

Thank you in advance!

How to define range of values in a formula?
 

Hi

You can't use the AND function inside Countif.
Instead, take one value from another

=COUNTIF(range,"<="&0.5)-COUNTIF(range,"<"&-0.5)

--
Regards

Roger Govier


"yasen" wrote in message
...
A B C D E
674 817 2 0 1
-6.0
-6.0
0.1


I need to count only the cells (in the first column) wich contain
values
within a specified range and display the result (if <0.5 in column C,
if
between -0.5 and 0.5 in column D and if 0.5 in column E). I used the
COUNTIF
function. It works well with the simple range definitions "<-0.5" and
"0.5".
How should I define the range between -0.5 and 0.5? I tried this
below, but
it seems I am wrong somewhere.
=COUNTIF(A30:A32;"AND(-0.5,<0.5)").

Thank you in advance!

How to define range of values in a formula?
 

Use 2 countifs, something like this:
=COUNTIF(A1:A4,"-0.5")-COUNTIF(A1:A4,"=0.5")

Adapt the range (and the separator?) to suit,
maybe for your example posted it'll be:
=COUNTIF(A30:A32;"-0.5")-COUNTIF(A30:A32;"=0.5")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"yasen" wrote:
A B C D E
674 817 2 0 1
-6.0
-6.0
0.1


I need to count only the cells (in the first column) wich contain values
within a specified range and display the result (if <0.5 in column C, if
between -0.5 and 0.5 in column D and if 0.5 in column E). I used the COUNTIF
function. It works well with the simple range definitions "<-0.5" and "0.5".
How should I define the range between -0.5 and 0.5? I tried this below, but
it seems I am wrong somewhere.
=COUNTIF(A30:A32;"AND(-0.5,<0.5)").

Thank you in advance!