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

  • Thread starter Thread starter Nigel
  • Start date Start date
N

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.
 
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
Click to expand...
=sumproduct((a2:a10>=0.5)*(a2:a10<2),a2:a10)
Success? wave it here, hit the YES below
 
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.

Nigel said:
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.
Click to expand...
 
Last edited:
Back
Top