Operator for "Does not equal to"

[Milind Keer]

Is there any operator for "Does not equal to" like <> is for "Not Equal To"
and = is for "Equal To"

I have a coloum with Name and City together
e.g. Mike - London, John - Leeds, Martha - London etc etc

Now I want only Exclude London Based people to calculate total salary for
remaining

My Table looks like below

Name&City--------------- Salary
Mike - London---------------100
John - Leeds-----------------300
Martha - London-------------200
Rita - Leed-------------------100
Geeta-Manchester-----------200

Now I want to exclude London bases people and calculate (SUM) salary of
other people.

Here result should be - 600

 

[Peo Sjoblom]

=SUMIF(A2:A10,"<>*London*",B2:B10)

if you want to reverse and get equal to something that contains London


=SUMIF(A2:A10,"*London*",B2:B10)

--


Regards,


Peo Sjoblom

 

[Mike H]

Maybe

=SUMPRODUCT((RIGHT(A2:A6,6)<>"London")*(B2:B6))

Mike

 

[ShaneDevenshire]

Hi,

The question David asks important. However assuming it is not, then:

=SUMIF(G8:G12,"*London",H8:H12)

Where the name&city are in column G and the amounts in H.

To make it more flexible:

=SUMIF(G8:G12,A1,H8:H12)

and in A1 enter *London

 

[Milind Keer]

Thanks a lot guys.... i didnt' know formulas in XL does support wild
characters... lack of knowledge...

Answer to David's question,

Does not eual to is the term XL is using in custom filter and thats why i
used that.

Basically... i want to exclude one word (which i know already) from the
cell...
like,
in above example i want to exclude "London" from Name and City column...

Thanks for all you help guys...
Milind Keer

 

[Milind Keer]

hey sorry guys that was a typo mistake....

dat should be "Does not contain" and not "Does not equal to"

Sorry for the confusion......

Cheers!!
Milind Keer

 

[Milind Keer]

Why following two formulas are not returning same result

=SUMIF(D110,"<>*London",Z1:Z10)

{=SUM(IF(D110 <> "*London",Z1:Z10,"False"))}

First Formula is returning appropriate (Correct) result and second formula
is returning Incorrect result.

No need to say I want to use second formula because I need to put one more
condition

{=SUM(IF((D110 <> "*London")*(E1:E10 = 0),Z1:Z10,"False"))}

Please advise.

Milind Keer

 

[Pete_UK]

You can't use wildcards in the second formula. You would need to use
something like:

=SUMPRODUCT((ISNUMBER(SEARCH("London",D110)))*(Z1:Z10))

or:

=SUMPRODUCT(--(ISNUMBER(SEARCH("London",D110))),Z1:Z10)

or, if you insist on the array formula:

=SUM(IF(ISNUMBER(SEARCH("London",D110)),Z1:Z10))

entered using CSE.

Hope this helps.

Pete

 

[Pete_UK]

Sorry, just realised you want not equal to London. Change it to this:

=SUM(IF(NOT(ISNUMBER(SEARCH("London",D110))),Z1:Z10))

or as in your last example:

=SUM(IF((NOT(ISNUMBER(SEARCH("London",D110))))*(E1:E10=0),Z1:Z10))

Commit using CSE.

Hope this helps.

Pete

 

[Milind Keer]

Pete

I did exactly d same... thanks anywayz...

milind

 

[Pete_UK]

You're welcome.

Pete