COUNTIF STOPS COUNTING

[bashtherat]

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.

 

[Glenn]

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

 

[Glenn]

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]

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

 

[Ron Rosenfeld]

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

 

[Glenn]

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.

 

[Ron Rosenfeld]

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

 

[Glenn]

It works fine in Excel 2007.

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

Could be...I'm using 2003.

 

[Ashish Mathur]

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

 

[bashtherat]

Your solution did not work.

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

 

[bashtherat]

Your solution did not work.

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

 

[Glenn]

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]

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