COUNTIF:count the number of times

swarfmaker · 10-02-2005, 07:05 AM

I am trying to use countif to count the number of times the value of cell I2
occurs in column D but the following does not work.
=COUNTIF(D6:D36,I2)
This returns zero but I know that there are 2 occurances of the number in I2

Any help appreciated.

Iain

Ron Rosenfeld · 10-02-2005, 09:05 AM

On Sun, 2 Oct 2005 12:04:11 +0100, "swarfmaker" <iain@swarfmaker.co.uk> wrote:

>I am trying to use countif to count the number of times the value of cell I2
>occurs in column D but the following does not work.
>=COUNTIF(D6:D36,I2)
>This returns zero but I know that there are 2 occurances of the number in I2
>
>Any help appreciated.
>
>Iain
>

You do not post examples of exactly what is in I2 and what is in column D.

However, if the values appear identical, then most likely one is text and the
other numeric. The "fix" would depend on which is which and how the values are
generated.

If you are looking for a value in I2 that may be a part of the value in column
D, then your syntax is incorrect.

--ron

swarfmaker · 10-02-2005, 09:05 AM

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:ufjvj11pij3h5jiohtq2oo5t9rkmkqv8on@4ax.com...
> On Sun, 2 Oct 2005 12:04:11 +0100, "swarfmaker" <iain@swarfmaker.co.uk>
> wrote:
>
>>I am trying to use countif to count the number of times the value of cell
>>I2
>>occurs in column D but the following does not work.
>>=COUNTIF(D6:D36,I2)
>>This returns zero but I know that there are 2 occurances of the number in
>>I2
>>
>>Any help appreciated.
>>
>>Iain
>>
>
> You do not post examples of exactly what is in I2 and what is in column D.
>
> However, if the values appear identical, then most likely one is text and
> the
> other numeric. The "fix" would depend on which is which and how the
> values are
> generated.
>
> If you are looking for a value in I2 that may be a part of the value in
> column
> D, then your syntax is incorrect.
>
>
> --ron

The values in column 2 are numeric (Blood Glucose values) in the range 2.5
to 28
The value in I2 is numeric, in this case 4 but can be changed by the user.
I need to count all values in column D that are less than the value in I2.

Iain

Max · 10-02-2005, 10:05 AM

"swarfmaker" wrote:
> .. I need to count all values in column D that are less than the value in
I2.

Instead of your earlier
>>=COUNTIF(D6:D36,I2)

Try: =COUNTIF(D6:D36,"<"&I2)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

swarfmaker · 10-02-2005, 11:05 AM

"Max" <demechanik@yahoo.com> wrote in message
news:%23rxMpj1xFHA.4032@TK2MSFTNGP15.phx.gbl...
> "swarfmaker" wrote:
>> .. I need to count all values in column D that are less than the value in
> I2.
>
> Instead of your earlier
>>>=COUNTIF(D6:D36,I2)
>
> Try: =COUNTIF(D6:D36,"<"&I2)
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>
Many thanks Max, that did the trick

Iain

Ron Rosenfeld · 10-02-2005, 11:05 AM

On Sun, 2 Oct 2005 14:02:09 +0100, "swarfmaker" <iain@swarfmaker.co.uk> wrote:

>
>"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
>news:ufjvj11pij3h5jiohtq2oo5t9rkmkqv8on@4ax.com...
>> On Sun, 2 Oct 2005 12:04:11 +0100, "swarfmaker" <iain@swarfmaker.co.uk>
>> wrote:
>>
>>>I am trying to use countif to count the number of times the value of cell
>>>I2
>>>occurs in column D but the following does not work.
>>>=COUNTIF(D6:D36,I2)
>>>This returns zero but I know that there are 2 occurances of the number in
>>>I2
>>>
>>>Any help appreciated.
>>>
>>>Iain
>>>
>>
>> You do not post examples of exactly what is in I2 and what is in column D.
>>
>> However, if the values appear identical, then most likely one is text and
>> the
>> other numeric. The "fix" would depend on which is which and how the
>> values are
>> generated.
>>
>> If you are looking for a value in I2 that may be a part of the value in
>> column
>> D, then your syntax is incorrect.
>>
>>
>> --ron
>
>The values in column 2 are numeric (Blood Glucose values) in the range 2.5
>to 28
>The value in I2 is numeric, in this case 4 but can be changed by the user.
>I need to count all values in column D that are less than the value in I2.
>
>Iain
>

Well, that's different than what you first posted.

To count all the values in column D that are *LESS THAN* the value in I2, use:

=COUNTIF(D:D,"<"&I2)

or, if it is just part of Column D that you are counting:

=COUNTIF(D6:D32,"<="&I2)

--ron