COUNTIF STOPS COUNTING

Glenn wrote:
> bashtherat wrote:
>> I used the following formula to count the number of times "NPRP
>> served" appears in a worksheet column: =COUNTIF(H3:H38,"*NPRP
>> served*"); but it stops counting if there is too much text before the
>> target text appears.
>>
>> Why does this happen and how do I get round it?
>>
>> Please help.
>
>
> Try this array formula (use CTRL+SHIFT+ENTER):
>
> =COUNT(--(FIND("NPRP served",H3:H38)>0))


For what it's worth, the ">0" seems not to be necessary.

Glenn wrote:
> bashtherat wrote:
>> I used the following formula to count the number of times "NPRP
>> served" appears in a worksheet column: =COUNTIF(H3:H38,"*NPRP
>> served*"); but it stops counting if there is too much text before the
>> target text appears.
>>
>> Why does this happen and how do I get round it?
>>
>> Please help.
>
>
> Try this array formula (use CTRL+SHIFT+ENTER):
>
> =COUNT(--(FIND("NPRP served",H3:H38)>0))

And without the ">0", the "--" also can be dropped, leaving you with this:

=COUNT(FIND("NPRP served",H3:H38))

On Mon, 3 Nov 2008 07:07:01 -0800, bashtherat
<bashtherat.DeleteThis@discussions.microsoft.com> wrote:

>I used the following formula to count the number of times "NPRP served"
>appears in a worksheet column: =COUNTIF(H3:H38,"*NPRP served*"); but it stops
>counting if there is too much text before the target text appears.
>
>Why does this happen and how do I get round it?
>
>Please help.


Shouldn't happen, unless you're running into a limit on the number of
characters in the cell, in which case the value wouldn't be there.

Please give example of an instance how this fails.
--ron

Ron Rosenfeld wrote:
> On Mon, 3 Nov 2008 07:07:01 -0800, bashtherat
> <bashtherat DeleteThis @discussions.microsoft.com> wrote:
>
>> I used the following formula to count the number of times "NPRP served"
>> appears in a worksheet column: =COUNTIF(H3:H38,"*NPRP served*"); but it stops
>> counting if there is too much text before the target text appears.
>>
>> Why does this happen and how do I get round it?
>>
>> Please help.
>
>
> Shouldn't happen, unless you're running into a limit on the number of
> characters in the cell, in which case the value wouldn't be there.
>
> Please give example of an instance how this fails.
> --ron


Anything over 255 characters is not found by COUNTIF.

On Mon, 03 Nov 2008 10:24:17 -0600, Glenn <address DeleteThis @not.valid> wrote:

>Ron Rosenfeld wrote:
>> On Mon, 3 Nov 2008 07:07:01 -0800, bashtherat
>> <bashtherat DeleteThis @discussions.microsoft.com> wrote:
>>
>>> I used the following formula to count the number of times "NPRP served"
>>> appears in a worksheet column: =COUNTIF(H3:H38,"*NPRP served*"); but it stops
>>> counting if there is too much text before the target text appears.
>>>
>>> Why does this happen and how do I get round it?
>>>
>>> Please help.
>>
>>
>> Shouldn't happen, unless you're running into a limit on the number of
>> characters in the cell, in which case the value wouldn't be there.
>>
>> Please give example of an instance how this fails.
>> --ron
>
>
>Anything over 255 characters is not found by COUNTIF.


It works fine in Excel 2007.

It must have been a limitation in earlier versions.
--ron

Ron Rosenfeld wrote:
> On Mon, 03 Nov 2008 10:24:17 -0600, Glenn <address.DeleteThis@not.valid> wrote:
>
>> Ron Rosenfeld wrote:
>>> On Mon, 3 Nov 2008 07:07:01 -0800, bashtherat
>>> <bashtherat.DeleteThis@discussions.microsoft.com> wrote:
>>>
>>>> I used the following formula to count the number of times "NPRP served"
>>>> appears in a worksheet column: =COUNTIF(H3:H38,"*NPRP served*"); but it stops
>>>> counting if there is too much text before the target text appears.
>>>>
>>>> Why does this happen and how do I get round it?
>>>>
>>>> Please help.
>>>
>>> Shouldn't happen, unless you're running into a limit on the number of
>>> characters in the cell, in which case the value wouldn't be there.
>>>
>>> Please give example of an instance how this fails.
>>> --ron
>>
>> Anything over 255 characters is not found by COUNTIF.
>
>
> It works fine in Excel 2007.
>
> It must have been a limitation in earlier versions.
> --ron

Could be...I'm using 2003.