I have a excel file using several worksheets.

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

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

Any ideas??
Trace

Does

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

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

Trace wrote:
I have a excel file using several worksheets.

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

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

Any ideas??
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, "*No OMS Order*")

(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-Pro


"Trace" wrote:

I have a excel file using several worksheets.

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

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

Any ideas??
Trace

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

"Trace" wrote:

I have a excel file using several worksheets.

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

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

Any ideas??
Trace

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

=F15="No OMS Order"

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)="No OMS Order")



"Trace" wrote:

I have a excel file using several worksheets.

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

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

Any ideas??
Trace