## [Solved] Ignoring negative value when summing a column

### [Solved] Ignoring negative value when summing a column

I'm trying to sum a column which has a negative number in it which I wish to ignore.
It is presently: J4=SUM(C6:C37). A negative value will always occure in the column to be summed which I wish to ignore.
When I add a value to B6 for instance, the correct value comes up in C6 because its formula is: =B6-B5. this continues down the column. However, C7 then shows a negative number because B7 has not yet got a value (that comes next day. Summing C6:C37 unfortunately takes that negative value into account. i'd like to ignore it in the J4 sum.
Cheers
Bob
Last edited by robleyd on Sat Dec 08, 2018 1:54 am, edited 3 times in total.
Reason: Title Edited
windows 10, Open Office 4.1.6
roberthawkins

Posts: 4
Joined: Fri Dec 07, 2018 11:34 am

### Re: Ignoring 0 value when summing

Welcome to the forum!

There is the standard function SUMIF() to do what you want:
Code: Select all   Expand viewCollapse view
`=SUMIF(C6:C37;">=0";C6:C37)`
Also consult the help to know in what way the parameter positions are interpreted.

===Editing===
I missed to mention the probably sometimes preferrable alternatives
Code: Select all   Expand viewCollapse view
`=SUMPRODUCT(C6:C37;C6:C37>=0)  and=SUMPRODUCT(C6:C37+ABS(C6:C37))/2`
The first formula actually relies on the basic functionality of SUMPRODUCT while the second one only makes use of the ForceArray specification for its parameters.
===End Edit===

roberthawkins (subject) wrote:Ignoring 0 value when summing
roberthawkins wrote:I'm trying to sum a column which has a negative number in it which I wish to ignore...
I would suggest you go back to your original post and edit the subject to get it consistent.
If the answer helped you, please also prefix "[SOLVED]" to the subject.
Last edited by Lupp on Fri Dec 07, 2018 4:09 pm, edited 1 time in total.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2477
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

### Re: Ignoring 0 value when summinga column.

Thanks Lupp, I've modified my request as best I can. Hope it makes sense now.
windows 10, Open Office 4.1.6
roberthawkins

Posts: 4
Joined: Fri Dec 07, 2018 11:34 am

### Re: Ignoring 0 value when summinga column.

If the suggested formula solved your problem you might again consider to prefix " [SOLVED]" to the subject of your original question. This is expected to help visitors to find solved requests among those they may get listed with their query.

The other hint was about the "Ignoring 0". You don't want to ignore 0, but negative values. The subject is misleading therefore. In addition: Ingnoring zeroes when summing doesn't change anything.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2477
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

### Re: Ignoring negative values when summinga column.

OK, not a mathematician so I didn't know what you meant. I didn't put solved because it isn't yet.
Thanks for the tip and I've ammended the subject line.
Cheers
Bob
windows 10, Open Office 4.1.6
roberthawkins

Posts: 4
Joined: Fri Dec 07, 2018 11:34 am

### Re: Ignoring 0 value when summinga column.

However, C7 then shows a negative number because B7 has not yet got a value (that comes next day.

Try to use the formula
Code: Select all   Expand viewCollapse view
`=IF(ISBLANK(B7);"";B7-B6)`

for eliminate negative values at non existing entries.

Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 7974
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: Ignoring 0 value when summinga column.

Thanks Zizi64, that worked a treat,
Bob
windows 10, Open Office 4.1.6
roberthawkins

Posts: 4
Joined: Fri Dec 07, 2018 11:34 am