how to specify "not equal to"?

[Melissa]

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.

 

[JMB]

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]

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

 

[JMB]

Glad to help. Thanks for the feedback.

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

 

[lalitmishra]

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

 

[lalitmishra]

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

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Shorter version

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


Gord

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


Gord Dibben MS Excel MVP

 

[Dan DeHaven]

formula

FYI, in formulas "not equal to" is specified as "<>"

Not sure if you're using Excel 2007 or not. 2007 give a great ne
function "SUMIFS" which allows form multiple "If's" as criteria in th
calculation.

Say your values are in C2:C50.....Use =SUMIFS(C2:C50,C2:C50,"<>0")
The first C2:C50 in the formula is the SUM range, the second C2:C50 i
the "criteria range1" which uses the "criteria1" third argument "<>0" t
exclude zeros.

I'm sure there are a dozen other ways to do it as well. If this isn'
meeting your needs please give a bit more info about the situation.

I can't understand why you would exclude zeros from a SUM as they ar
normally excluded by nature of the value, but maybe there is somethin
I'm missing.

Da