Operator for "Does not equal to"
 

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

Operator for "Does not equal to"
 

=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

"Milind Keer" wrote in message
...
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

Operator for "Does not equal to"
 

Maybe

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

Mike

"Milind Keer" wrote:

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

Operator for "Does not equal to"
 

What is your difference between "Does not equal to" and "Not Equal To" ?
--
David Biddulph

"Milind Keer" wrote in message
...
Is there any operator for "Does not equal to" like < is for "Not Equal
To"
and = is for "Equal To"
....

Operator for "Does not equal to"
 

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

--
Cheers,
Shane Devenshire


"Milind Keer" wrote:

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

Operator for "Does not equal to"
 

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



"ShaneDevenshire" wrote:

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

--
Cheers,
Shane Devenshire


"Milind Keer" wrote:

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

Operator for "Does not equal to"
 

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" wrote:

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



"ShaneDevenshire" wrote:

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

--
Cheers,
Shane Devenshire


"Milind Keer" wrote:

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

Operator for "Does not equal to"
 

Why following two formulas are not returning same result

=SUMIF(D1:D10,"<*London",Z1:Z10)

{=SUM(IF(D1:D10 < "*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((D1:D10 < "*London")*(E1:E10 = 0),Z1:Z10,"False"))}

Please advise.

Milind Keer







"Milind Keer" wrote:

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" wrote:

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



"ShaneDevenshire" wrote:

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

--
Cheers,
Shane Devenshire


"Milind Keer" wrote:

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

Operator for "Does not equal to"
 

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

=SUMPRODUCT((ISNUMBER(SEARCH("London",D1:D10)))*(Z 1:Z10))

or:

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

or, if you insist on the array formula:

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

entered using CSE.

Hope this helps.

Pete

On Sep 18, 11:24*am, Milind Keer
wrote:
Why following two formulas are not returning same result

=SUMIF(D1:D10,"<*London",Z1:Z10)

{=SUM(IF(D1:D10 < "*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((D1:D10 < "*London")*(E1:E10 = 0),Z1:Z10,"False"))}

Please advise.

Milind Keer



"Milind Keer" wrote:
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" wrote:

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

"ShaneDevenshire" wrote:

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

--
Cheers,
Shane Devenshire

"Milind Keer" wrote:

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 *- Hide quoted text -

- Show quoted text -

Operator for "Does not equal to"
 

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

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

or as in your last example:

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

Commit using CSE.

Hope this helps.

Pete

On Sep 18, 12:59*pm, Pete_UK wrote:
You can't use wildcards in the second formula. You would need to use
something like:

=SUMPRODUCT((ISNUMBER(SEARCH("London",D1:D10)))*(Z 1:Z10))

or:

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

or, if you insist on the array formula:

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

entered using CSE.

Hope this helps.

Pete

On Sep 18, 11:24*am, Milind Keer



wrote:
Why following two formulas are not returning same result

=SUMIF(D1:D10,"<*London",Z1:Z10)

{=SUM(IF(D1:D10 < "*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((D1:D10 < "*London")*(E1:E10 = 0),Z1:Z10,"False"))}

Please advise.

Milind Keer

"Milind Keer" wrote:
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" wrote:

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

"ShaneDevenshire" wrote:

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

--
Cheers,
Shane Devenshire

"Milind Keer" wrote:

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 *- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Operator for "Does not equal to"
 

Pete

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

milind



"Pete_UK" wrote:

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

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

or as in your last example:

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

Commit using CSE.

Hope this helps.

Pete

On Sep 18, 12:59 pm, Pete_UK wrote:
You can't use wildcards in the second formula. You would need to use
something like:

=SUMPRODUCT((ISNUMBER(SEARCH("London",D1:D10)))*(Z 1:Z10))

or:

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

or, if you insist on the array formula:

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

entered using CSE.

Hope this helps.

Pete

On Sep 18, 11:24 am, Milind Keer



wrote:
Why following two formulas are not returning same result

=SUMIF(D1:D10,"<*London",Z1:Z10)

{=SUM(IF(D1:D10 < "*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((D1:D10 < "*London")*(E1:E10 = 0),Z1:Z10,"False"))}

Please advise.

Milind Keer

"Milind Keer" wrote:
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" wrote:

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

"ShaneDevenshire" wrote:

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

--
Cheers,
Shane Devenshire

"Milind Keer" wrote:

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 - Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Operator for "Does not equal to"
 

You're welcome.

Pete

On Sep 18, 1:56*pm, Milind Keer
wrote:
Pete

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

milind