software

How do I omit a cell that contains a negative number in an addition formula?
I can't use sum because there are cells in between.

maybe, =SUMIF(A1:A10,quot;gt;0quot;)
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 amp; 2003

quot;LD6892quot; gt; wrote in message
...
gt; How do I omit a cell that contains a negative number in an addition
gt; formula?
gt; I can't use sum because there are cells in between.
Try:

=SUMIF(A1:A10,quot;gt;0quot;)

HTH

quot;LD6892quot; wrote:

gt; How do I omit a cell that contains a negative number in an addition formula?
gt; I can't use sum because there are cells in between.

=SUM(IF(A2:A30gt;0,A2:A30))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

===
Or not entered as an array formula:
=SUMPRODUCT(--(A2:A30gt;0),(A2:A30))

(Still can't use the whole column, though.)

LD6892 wrote:
gt;
gt; How do I omit a cell that contains a negative number in an addition formula?
gt; I can't use sum because there are cells in between.

--

Dave Peterson

How many range areas are you trying to sum? Are there only a few or many?
Can you give an example of what you think the formula might look like?

***********
Regards,
Ron

XL2002, WinXPquot;LD6892quot; wrote:

gt; How do I omit a cell that contains a negative number in an addition formula?
gt; I can't use sum because there are cells in between.

I have 30 columns and need to add every other column, but don't want to
include the cell if it's a negative.
=A2 C2 E2 G2, etc.

Someone else designed the spreadsheet and I was hoping not to have to redo
the whole thing to make it a sum formula.

Thanks

quot;Ron Coderrequot; wrote:

gt; How many range areas are you trying to sum? Are there only a few or many?
gt; Can you give an example of what you think the formula might look like?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;LD6892quot; wrote:
gt;
gt; gt; How do I omit a cell that contains a negative number in an addition formula?
gt; gt; I can't use sum because there are cells in between.

Thanks, but how do I use a sum formula when I need to add every other column?
this is the current formula
=A2 C2 E2 G2 etc.

quot;Toppersquot; wrote:

gt; Try:
gt;
gt; =SUMIF(A1:A10,quot;gt;0quot;)
gt;
gt; HTH
gt;
gt; quot;LD6892quot; wrote:
gt;
gt; gt; How do I omit a cell that contains a negative number in an addition formula?
gt; gt; I can't use sum because there are cells in between.

Try something like this:

A1: =SUMPRODUCT((MOD(COLUMN(A2:AD2),2)=1)*(A2:AD2gt;0)*A 2:AD2)

That formula add the values from Row_2 in odd-numbered columns where the
cell value is greater than zero.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXPquot;LD6892quot; wrote:

gt; I have 30 columns and need to add every other column, but don't want to
gt; include the cell if it's a negative.
gt; =A2 C2 E2 G2, etc.
gt;
gt; Someone else designed the spreadsheet and I was hoping not to have to redo
gt; the whole thing to make it a sum formula.
gt;
gt; Thanks
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; How many range areas are you trying to sum? Are there only a few or many?
gt; gt; Can you give an example of what you think the formula might look like?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP
gt; gt;
gt; gt;
gt; gt; quot;LD6892quot; wrote:
gt; gt;
gt; gt; gt; How do I omit a cell that contains a negative number in an addition formula?
gt; gt; gt; I can't use sum because there are cells in between.

This formula worked for me (note, expand to fit your range):
=SUMPRODUCT(--(A2:M2gt;0),--(MOD(COLUMN(A2:M2),2)=1),(A2:M2))
--
Kevin Vaughnquot;LD6892quot; wrote:

gt; I have 30 columns and need to add every other column, but don't want to
gt; include the cell if it's a negative.
gt; =A2 C2 E2 G2, etc.
gt;
gt; Someone else designed the spreadsheet and I was hoping not to have to redo
gt; the whole thing to make it a sum formula.
gt;
gt; Thanks
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; How many range areas are you trying to sum? Are there only a few or many?
gt; gt; Can you give an example of what you think the formula might look like?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP
gt; gt;
gt; gt;
gt; gt; quot;LD6892quot; wrote:
gt; gt;
gt; gt; gt; How do I omit a cell that contains a negative number in an addition formula?
gt; gt; gt; I can't use sum because there are cells in between.

Thanks!!!!

quot;Ron Coderrequot; wrote:

gt; Try something like this:
gt;
gt; A1: =SUMPRODUCT((MOD(COLUMN(A2:AD2),2)=1)*(A2:AD2gt;0)*A 2:AD2)
gt;
gt; That formula add the values from Row_2 in odd-numbered columns where the
gt; cell value is greater than zero.
gt;
gt; Is that something you can work with?
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP
gt;
gt;
gt; quot;LD6892quot; wrote:
gt;
gt; gt; I have 30 columns and need to add every other column, but don't want to
gt; gt; include the cell if it's a negative.
gt; gt; =A2 C2 E2 G2, etc.
gt; gt;
gt; gt; Someone else designed the spreadsheet and I was hoping not to have to redo
gt; gt; the whole thing to make it a sum formula.
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; How many range areas are you trying to sum? Are there only a few or many?
gt; gt; gt; Can you give an example of what you think the formula might look like?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;LD6892quot; wrote:
gt; gt; gt;
gt; gt; gt; gt; How do I omit a cell that contains a negative number in an addition formula?
gt; gt; gt; gt; I can't use sum because there are cells in between.