## [Solved] SUMIF ">0" with subtraction

### [Solved] SUMIF ">0" with subtraction

Been trying to get this for a while lol.
I have got the SUMIf of data to only display 0 if its negative.
Now I'm looking to subtract a value from it But retain the if negative only display 0
=SUMIF(G38:G44;">0")
so far this works to only display 0 if negative. But I'm looking to use the sum and subtract from a different cell.
=F41-SUMIF(G38:G44;">0") which this works just doesn't retain the ">0"
Have tried a few variations of this as well as a few formatting options but still getting me an error so I thought since I'm looking on the forum for an answer why not ask.
so thanks in advance for the help

RusselB thanks for that solution
=max(0;sumif(G38:G44;">0")-F41)
Last edited by hairtothepie on Tue Jan 29, 2019 10:52 am, edited 1 time in total.
OpenOffice 4.1.6 / WIN 10 Pro V:1809 Os Build 17763.253
hairtothepie

Posts: 4
Joined: Tue Jan 29, 2019 5:05 am

### Re: SUMIF ">0" with subtraction

I am confused about what you want to do. The condition in the SUMIF() function does not change what is displayed in the cell, it determines what values are included in the sum. Say A1 = 10, A2 = -5, A3 = 5.
Code: Select all   Expand viewCollapse view
`SUMIF(A1:A3;">0")`

will return 15 because it ignores the -5. Is that what you want? Your question is worded as if you want
Code: Select all   Expand viewCollapse view
`IF(SUM(A1:A3) < 0; 0; SUM(A1:A3)`

which will return 10 because the three cells add to 10, which is not <0.

Do you want to exclude negative values from the sum or do you want a zero displayed if the sum is negative?
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7618
Joined: Sat Nov 08, 2008 8:08 pm

### Re: SUMIF ">0" with subtraction

Code: Select all   Expand viewCollapse view
`=max(0;F41-sumif(G38:G44;">0"))`

however, you also state that you want to subtract a number from the SUMIF total, which is different from your 2nd code, which subtracts the SUMIF total from another number.
If you are subtracting the number from the SUMIF, then try
Code: Select all   Expand viewCollapse view
`=max(0;sumif(G38:G44;">0")-F41)`

I have used the same cell addresses and ranges that you have given in the original post.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 5984
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: SUMIF ">0" with subtraction

RusselB that second one worked wonders thanks.
now ill need to do some reading on what makes the =max(0: do the trick cause i was trying a bunch of things but this was a time saver for sure.
As someone who usually just tries and reads till he gets it. Thank You
OpenOffice 4.1.6 / WIN 10 Pro V:1809 Os Build 17763.253
hairtothepie

Posts: 4
Joined: Tue Jan 29, 2019 5:05 am

### Re: SUMIF ">0" with subtraction

FJCC wrote:I am confused about what you want to do. The condition in the SUMIF() function does not change what is displayed in the cell, it determines what values are included in the sum. Say A1 = 10, A2 = -5, A3 = 5.
Code: Select all   Expand viewCollapse view
`SUMIF(A1:A3;">0")`

will return 15 because it ignores the -5. Is that what you want? Your question is worded as if you want
Code: Select all   Expand viewCollapse view
`IF(SUM(A1:A3) < 0; 0; SUM(A1:A3)`

which will return 10 because the three cells add to 10, which is not <0.

Do you want to exclude negative values from the sum or do you want a zero displayed if the sum is negative?

A solution has been reached thank you for your input.
I was looking to get the sum of G38:G44 which will always be positive numbers or empty. while the need to subtract a cell F41.
I will always have a value in F41 that will subtract. I needed to keep this 0 vs negative since my sum isn't always going to have data.
OpenOffice 4.1.6 / WIN 10 Pro V:1809 Os Build 17763.253
hairtothepie

Posts: 4
Joined: Tue Jan 29, 2019 5:05 am

### Re: [Solved] SUMIF ">0" with subtraction

Max(0;sumif(G38:G44;">0")-F41)
As a human, you would do the SUMIF part first, then subtract F41
Now compare that answer to 0 and put the greater number (the MAX) into the cell
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 5984
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: [Solved] SUMIF ">0" with subtraction

Yeah, see I was putting to much focus on the internal command and thinking the ">0" was doing what I needed. Just couldn't get my ( ) to work the way I wanted.
Now ">0" only targets the group and applies the sum to the cells that are greater than 0 right?
So since my group is always going to be either empty or positive I could remove it. At that point, I would only need the sum not sumif
Max(0;sum(G38:G44)-F41)

The max(0 can be used to specify any value?
Say max(-4?
Sorry, I haven't read into the max yet. But figured I would ask.
Thanks

ok so I just tested the few things I asked and it works as I figured as for the sumif and ">0" only grabbing positive number that makes sense too. Was too focused that the ">0" was keeping it positive and how to make it work then looking at a new angle
OpenOffice 4.1.6 / WIN 10 Pro V:1809 Os Build 17763.253
hairtothepie

Posts: 4
Joined: Tue Jan 29, 2019 5:05 am