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.
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).
> 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.
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.
> 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.
> 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
On Tue, 8 Sep 2009 05:36:01 -0700, lalitmishra
<lalitmishra@discussions.microsoft.com> 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
>=SUMIF(A1:A11,"<>0",A1:A11)
>
>
>Gord Dibben MS Excel MVP
>
>On Tue, 8 Sep 2009 05:36:01 -0700, lalitmishra
><lalitmishra@discussions.microsoft.com> 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
> 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
lalitmishra;480279 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.i like nat a other formula i choose a date of
> formula
FYI, in formulas "not equal to" is specified as "<>"
Not sure if you're using Excel 2007 or not. 2007 give a great new
function "SUMIFS" which allows form multiple "If's" as criteria in the
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 is
the "criteria range1" which uses the "criteria1" third argument "<>0" to
exclude zeros.
I'm sure there are a dozen other ways to do it as well. If this isn't
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 are
normally excluded by nature of the value, but maybe there is something
I'm missing.
Question
Reply
Top
Answer
Notify me of replies