COUNTIF STOPS COUNTING
 

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.

COUNTIF STOPS COUNTING
 

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))

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.

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))

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

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

COUNTIF STOPS COUNTING
 

On Mon, 3 Nov 2008 07:07:01 -0800, 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.


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

COUNTIF STOPS COUNTING
 

Ron Rosenfeld wrote:
On Mon, 3 Nov 2008 07:07:01 -0800, 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.


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.

COUNTIF STOPS COUNTING
 

On Mon, 03 Nov 2008 10:24:17 -0600, Glenn wrote:

Ron Rosenfeld wrote:
On Mon, 3 Nov 2008 07:07:01 -0800, 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.


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

COUNTIF STOPS COUNTING
 

Ron Rosenfeld wrote:
On Mon, 03 Nov 2008 10:24:17 -0600, Glenn wrote:

Ron Rosenfeld wrote:
On Mon, 3 Nov 2008 07:07:01 -0800, 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.

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.

COUNTIF STOPS COUNTING
 

Hi,

Instead of FIND(), you could use the SEARCH() function as well provided your
search is not case sensitive.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Glenn" wrote in message
...
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))

COUNTIF STOPS COUNTING
 

Your solution did not work.

I am running windows xp with office 2003 and the text box contains more than
255 characters.

"Ashish Mathur" wrote:

Hi,

Instead of FIND(), you could use the SEARCH() function as well provided your
search is not case sensitive.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Glenn" wrote in message
...
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))

COUNTIF STOPS COUNTING
 

Your solution did not work.

I am running windows xp with office 2003 and the text box contains more than
255 characters.


"Glenn" wrote:

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))

COUNTIF STOPS COUNTING
 

Define "did not work". Did you remember to array-enter (CTRL+SHIFT+ENTER) the
formula? What results did you get and what were you expecting?

Paste your exact formula and the data from one of the target cells you believe
are not being counted properly.

My latest test worked for a cell with over 32,000 characters.

bashtherat wrote:
Your solution did not work.

I am running windows xp with office 2003 and the text box contains more than
255 characters.


"Glenn" wrote:

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))

COUNTIF STOPS COUNTING
 

Apologies Glenn

I used (CTRL+SHIFT_ENTER) before typing in the formula.

Once I pressed it after typing it worked great.

I'm just a novice!

Thanks for all your help.

Bash

"Glenn" wrote:

Define "did not work". Did you remember to array-enter (CTRL+SHIFT+ENTER) the
formula? What results did you get and what were you expecting?

Paste your exact formula and the data from one of the target cells you believe
are not being counted properly.

My latest test worked for a cell with over 32,000 characters.

bashtherat wrote:
Your solution did not work.

I am running windows xp with office 2003 and the text box contains more than
255 characters.


"Glenn" wrote:

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))