Excel - count if a number falls within an array in a column & add

[Nigel]

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.

 

[Max]

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

 

[ryguy7272]

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

 

[tnazirov]

Hi,

Your problem can be solved by means of function DCOUNT.

Let's assume, data are in A1:A5, and in À1 the heading is placed:

Title
1.9
1
3.2
0.5


Criteria of calculation are placed in C1 : D2

Title Title
>=0.5 <2

Then having placed the formula:

=DCOUNT (A1:A5, "Title", C1 : D2)

In any place of the table we will receive expected result.
Regards.

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.