software

I have a excel file using several worksheets.

On most of the worksheets the formula =COUNTIF(F10:F232, quot;No OMS Orderquot;)
works. However -- on 2 of the work sheets, the formula will not work.

I have this formula set up for =COUNTIF (F10:F232, quot;Successfulquot;) and
=COUNTIF (F10:F232, quot;Adjudicatedquot;) and it works great... but on this one work
sheet, as soon as I use the words quot;No OMS Orderquot; I get a 0 records. Even
though there are several records that fit.

Any ideas??
Trace

Does

=COUNTIF(F10:F232,quot;*No OMS Order*quot;)

succeed? If it does, the entries in F10:F232 must have excessive chars
(e.g., spaces) around them.

Trace wrote:
gt; I have a excel file using several worksheets.
gt;
gt; On most of the worksheets the formula =COUNTIF(F10:F232, quot;No OMS Orderquot;)
gt; works. However -- on 2 of the work sheets, the formula will not work.
gt;
gt; I have this formula set up for =COUNTIF (F10:F232, quot;Successfulquot;) and
gt; =COUNTIF (F10:F232, quot;Adjudicatedquot;) and it works great... but on this one work
gt; sheet, as soon as I use the words quot;No OMS Orderquot; I get a 0 records. Even
gt; though there are several records that fit.
gt;
gt; Any ideas??
gt; Trace

Trace:
You might have stray spaces before, after, or within your criteria text.

To check for spaces before/after, try something like this:
=COUNTIF(F10:F232, quot;*No OMS Order*quot;)

(note the asterisks in that formula)

If that works, you need to scrub your data.

Here's one way:
Select F10:F232
Edit|Replace
Find what: *No OMS Order*
Replace with: No OMS Order
Click the [Replace All] button

If the spaces are within the text, you may need to do include more asterisks.
Example:
Find what: *No*OMS*Order*

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Tracequot; wrote:

gt; I have a excel file using several worksheets.
gt;
gt; On most of the worksheets the formula =COUNTIF(F10:F232, quot;No OMS Orderquot;)
gt; works. However -- on 2 of the work sheets, the formula will not work.
gt;
gt; I have this formula set up for =COUNTIF (F10:F232, quot;Successfulquot;) and
gt; =COUNTIF (F10:F232, quot;Adjudicatedquot;) and it works great... but on this one work
gt; sheet, as soon as I use the words quot;No OMS Orderquot; I get a 0 records. Even
gt; though there are several records that fit.
gt;
gt; Any ideas??
gt; Trace

It may be a simple typo... note the space in your formula after COUNTIF.

quot;Tracequot; wrote:

gt; I have a excel file using several worksheets.
gt;
gt; On most of the worksheets the formula =COUNTIF(F10:F232, quot;No OMS Orderquot;)
gt; works. However -- on 2 of the work sheets, the formula will not work.
gt;
gt; I have this formula set up for =COUNTIF (F10:F232, quot;Successfulquot;) and
gt; =COUNTIF (F10:F232, quot;Adjudicatedquot;) and it works great... but on this one work
gt; sheet, as soon as I use the words quot;No OMS Orderquot; I get a 0 records. Even
gt; though there are several records that fit.
gt;
gt; Any ideas??
gt; Trace

What happens if you directly compare a cell that contains the string to quot;No
OMS Orderquot;? So, if F15 contains that string and you type

=F15=quot;No OMS Orderquot;

in an empty cell, what do you get? If FALSE it's likely because you have
leading or trailing spaces in F15. In that case you can use

=SUMPRODUCT(--(TRIM(F10:F232)=quot;No OMS Orderquot;)
quot;Tracequot; wrote:

gt; I have a excel file using several worksheets.
gt;
gt; On most of the worksheets the formula =COUNTIF(F10:F232, quot;No OMS Orderquot;)
gt; works. However -- on 2 of the work sheets, the formula will not work.
gt;
gt; I have this formula set up for =COUNTIF (F10:F232, quot;Successfulquot;) and
gt; =COUNTIF (F10:F232, quot;Adjudicatedquot;) and it works great... but on this one work
gt; sheet, as soon as I use the words quot;No OMS Orderquot; I get a 0 records. Even
gt; though there are several records that fit.
gt;
gt; Any ideas??
gt; Trace