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.
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,">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" <[email protected]> wrote in message
news:[email protected]...
> 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.
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.
=SUM(IF(A2:A30>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:A30>0),(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
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.
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.
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.
Try something like this:
A1: =SUMPRODUCT((MOD(COLUMN(A2:AD2),2)=1)*(A2:AD2>0)*A2: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.
This formula worked for me (note, expand to fit your range):
=SUMPRODUCT(--(A2:M2>0),--(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.
Thanks!!!!
"Ron Coderre" wrote:
> Try something like this:
>
> A1: =SUMPRODUCT((MOD(COLUMN(A2:AD2),2)=1)*(A2:AD2>0)*A2: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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks