Trying to write a formula that creates a count if a number in a cell in an
array (a column) falls within a certain range, and then adds them up: e.g.
1.9
1
3.2
0.5

I want to count if a number is greater (or equal) to 0.5 but less than 2,
and then add them up to return a value (3). I've tried IF, COUNTIF, AND, OR
but can't get it to work - Any ideas? Nigel.

Sumproduct should prove useful here ...
.. count if a number is greater (or equal) to 0.5 but less than 2
=sumproduct((a2:a10=0.5)*(a2:a10<2))

.. then add them up to return a value
=sumproduct((a2:a10=0.5)*(a2:a10<2),a2:a10)
Success? wave it here, hit the YES below
--
Max
Singapore
---
"Nigel" wrote:
Trying to write a formula that creates a count if a number in a cell in an
array (a column) falls within a certain range, and then adds them up: e.g.
1.9
1
3.2
0.5

I want to count if a number is greater (or equal) to 0.5 but less than 2,
and then add them up to return a value (3). I've tried IF, COUNTIF, AND, OR
but can't get it to work - Any ideas? Nigel.

Here's another way of doing it:
=SUMPRODUCT(--(A1:A13=0.5),--(A1:A13<2))


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Max" wrote:

Sumproduct should prove useful here ...
.. count if a number is greater (or equal) to 0.5 but less than 2
=sumproduct((a2:a10=0.5)*(a2:a10<2))

.. then add them up to return a value
=sumproduct((a2:a10=0.5)*(a2:a10<2),a2:a10)
Success? wave it here, hit the YES below
--
Max
Singapore
---
"Nigel" wrote:
Trying to write a formula that creates a count if a number in a cell in an
array (a column) falls within a certain range, and then adds them up: e.g.
1.9
1
3.2
0.5

I want to count if a number is greater (or equal) to 0.5 but less than 2,
and then add them up to return a value (3). I've tried IF, COUNTIF, AND, OR
but can't get it to work - Any ideas? Nigel.