| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF not counting properly | Excel Discussion (Misc queries) | |||
| COUNTIF counting incorrectly | Excel Worksheet Functions | |||
| COUNTIF not counting | Excel Discussion (Misc queries) | |||
| Countif and Text - not counting | Excel Discussion (Misc queries) | |||
| Using COUNTIF for Counting Words? | Excel Worksheet Functions | |||
Linear Mode
