how to specify "not equal to"?
 

To give a simple example, I would like to sum a range of cells only if the
values are not 0. So the sum should include both positive and negative
numbers. What would the formula look like?
Thank you very much.

how to specify "not equal to"?
 

If I may, let's change the example to summing numbers in A1:A7 that are not
equal to 2. Obviously, it makes no sense to exclude 0 from a sum
calculation.

=SUMIF(A1:A7,"<2")
or if B1 = 2
=SUMIF(A1:A7,"<"&B1)

However, the idea is the same for 0 if that's truly what you want. If it is
not helpful, please post back w/some more details about what you are actually
trying to do. If you have multiple conditions or more complex conditions,
you may need to use a different function (such as sumproduct).

http://xldynamic.com/source/xld.SUMPRODUCT.html



"Melissa" wrote:

To give a simple example, I would like to sum a range of cells only if the
values are not 0. So the sum should include both positive and negative
numbers. What would the formula look like?
Thank you very much.

how to specify "not equal to"?
 

Yes you are right, it makes no sense whatsoever to sum up a range and exclude
0 from it :o)
I just wanted to know what the "not equal to" sign was which you've provided
as "<". Thanks very much!

"JMB" wrote:

If I may, let's change the example to summing numbers in A1:A7 that are not
equal to 2. Obviously, it makes no sense to exclude 0 from a sum
calculation.

=SUMIF(A1:A7,"<2")
or if B1 = 2
=SUMIF(A1:A7,"<"&B1)

However, the idea is the same for 0 if that's truly what you want. If it is
not helpful, please post back w/some more details about what you are actually
trying to do. If you have multiple conditions or more complex conditions,
you may need to use a different function (such as sumproduct).

http://xldynamic.com/source/xld.SUMPRODUCT.html



"Melissa" wrote:

To give a simple example, I would like to sum a range of cells only if the
values are not 0. So the sum should include both positive and negative
numbers. What would the formula look like?
Thank you very much.

how to specify "not equal to"?
 

Glad to help. Thanks for the feedback.

"Melissa" wrote:

Yes you are right, it makes no sense whatsoever to sum up a range and exclude
0 from it :o)
I just wanted to know what the "not equal to" sign was which you've provided
as "<". Thanks very much!

"JMB" wrote:

If I may, let's change the example to summing numbers in A1:A7 that are not
equal to 2. Obviously, it makes no sense to exclude 0 from a sum
calculation.

=SUMIF(A1:A7,"<2")
or if B1 = 2
=SUMIF(A1:A7,"<"&B1)

However, the idea is the same for 0 if that's truly what you want. If it is
not helpful, please post back w/some more details about what you are actually
trying to do. If you have multiple conditions or more complex conditions,
you may need to use a different function (such as sumproduct).

http://xldynamic.com/source/xld.SUMPRODUCT.html



"Melissa" wrote:

To give a simple example, I would like to sum a range of cells only if the
values are not 0. So the sum should include both positive and negative
numbers. What would the formula look like?
Thank you very much.

how to specify "not equal to"?
 

"Melissa" wrote:

To give a simple example, I would like to sum a range of cells only if the
values are not 0. So the sum should include both positive and negative
numbers. What would the formula look like?
Thank you very much.plz halp mee to you than any thing question to serial answwer

how to specify "not equal to"?
 

"Melissa" wrote:

To give a simple example, I would like to sum a range of cells only if the
values are not 0. So the sum should include both positive and negative
numbers. What would the formula look like?
Thank you very much.i like nat a other formula i choose a date of formula

how to specify "not equal to"?
 

=SUMIF(A1:A11,"<0",A1:A11)


Gord Dibben MS Excel MVP

On Tue, 8 Sep 2009 05:36:01 -0700, lalitmishra
wrote:



"Melissa" wrote:

To give a simple example, I would like to sum a range of cells only if the
values are not 0. So the sum should include both positive and negative
numbers. What would the formula look like?
Thank you very much.plz halp mee to you than any thing question to serial answwer

how to specify "not equal to"?
 

Shorter version

=SUMIF(A1:A11,"<0")


Gord

On Tue, 08 Sep 2009 08:44:39 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

=SUMIF(A1:A11,"<0",A1:A11)


Gord Dibben MS Excel MVP

On Tue, 8 Sep 2009 05:36:01 -0700, lalitmishra
wrote:



"Melissa" wrote:

To give a simple example, I would like to sum a range of cells only if the
values are not 0. So the sum should include both positive and negative
numbers. What would the formula look like?
Thank you very much.plz halp mee to you than any thing question to serial answwer