Criteria

[veryeavy]

Hi All,

Whilst I know the basics of criteria (in this case to be used in a DSUM
formula) and have looked at the examples in the Help system I am struggling
with this:

Say one of my criteria is:

Cost Center
5850

which I can also write as:

Cost Center
="=5850"

How can I write the criteria of not equal to 5850?

I have tried:

Cost Center
<>5850

and

Cost Center
="<>5850"

and neither seems to work.

Please help.

Thanks and Best Regards,

Matt

 

[Dave Peterson]

If the cost center entry was a real number (=isnumber() returned true), then
both your suggestions worked ok for me.

If your cost center was text (cell preformatted as Text or started with an
apostrophe), then it failed.

This kind of worked for me:
Cost Center
<>5850*

But this will show the text 5850 and 5850QWER and 5850ASDF as well.

You may want to try using the technique at Debra Dalgleish's site:
http://contextures.com/xladvfilter02.html#Number

My cost center column is column B and the header is in B1.

My criteria range is J1:J2.

I left J1 blank
and put this in J2:
=B2="5850"

And the advanced filter worked to show just the text extries 5850.

If I had a mixture of both numbers 5850 and text 5850, then this worked ok:

I left J1 blank
and put this in J2:
=OR(B2="5850",B2=5850)

 

[veryeavy]

Thanks Dave,

You are a legend.

And yes, the "numbers" were text ... I have taken your sorta works solution
with its caveat.

Cheers,

Matt

 

[Dave Peterson]

Each time I have trouble with advanced filter, I visit Debra's site.

You may want to bookmark her site, too????