| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Excel solver should allow "equal to" value to be a selected cell. | Excel Worksheet Functions | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| sort data rows "greater than or equal" criteria in another cell | Excel Worksheet Functions | |||
Linear Mode
