omit a negative number
 

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.

omit a negative number
 

maybe, =SUMIF(A1:A10,"0")
--
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 & 2003

"LD6892" wrote in message
...
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.

omit a negative number
 

Try:

=SUMIF(A1:A10,"0")

HTH

"LD6892" wrote:

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.

omit a negative number
 

=SUM(IF(A2:A300,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:A300),(A2:A30))

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

LD6892 wrote:

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.

--

Dave Peterson

omit a negative number
 

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, WinXP


"LD6892" wrote:

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.

omit a negative number
 

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

"Ron Coderre" wrote:

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, WinXP


"LD6892" wrote:

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.

omit a negative number
 

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.

"Toppers" wrote:

Try:

=SUMIF(A1:A10,"0")

HTH

"LD6892" wrote:

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.

omit a negative number
 

Try something like this:

A1: =SUMPRODUCT((MOD(COLUMN(A2:AD2),2)=1)*(A2:AD20)*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, WinXP


"LD6892" wrote:

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

"Ron Coderre" wrote:

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, WinXP


"LD6892" wrote:

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.

omit a negative number
 

This formula worked for me (note, expand to fit your range):
=SUMPRODUCT(--(A2:M20),--(MOD(COLUMN(A2:M2),2)=1),(A2:M2))
--
Kevin Vaughn


"LD6892" wrote:

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

"Ron Coderre" wrote:

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, WinXP


"LD6892" wrote:

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.

omit a negative number
 

Thanks!!!!

"Ron Coderre" wrote:

Try something like this:

A1: =SUMPRODUCT((MOD(COLUMN(A2:AD2),2)=1)*(A2:AD20)*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, WinXP


"LD6892" wrote:

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

"Ron Coderre" wrote:

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, WinXP


"LD6892" wrote:

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.