[Solved] SUMIF ">0" with subtraction

Discuss the spreadsheet application

[Solved] SUMIF ">0" with subtraction

Postby hairtothepie » Tue Jan 29, 2019 5:16 am

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

Postby FJCC » Tue Jan 29, 2019 6:10 am

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
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7354
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: SUMIF ">0" with subtraction

Postby RusselB » Tue Jan 29, 2019 8:52 am

Based on your two example codes, I think your looking for
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.
User avatar
RusselB
Moderator
 
Posts: 5601
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: SUMIF ">0" with subtraction

Postby hairtothepie » Tue Jan 29, 2019 10:55 am

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

Postby hairtothepie » Tue Jan 29, 2019 11:02 am

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

Postby RusselB » Tue Jan 29, 2019 11:11 am

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.
User avatar
RusselB
Moderator
 
Posts: 5601
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Postby hairtothepie » Tue Jan 29, 2019 11:24 am

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


Return to Calc

Who is online

Users browsing this forum: MSN [Bot] and 23 guests